Index Full Scan

I received an urgent priority ticket to resolve a performance issue for a sql statement which was running for 4-5 hours and throwing snapshot too old error.

The query was based on finding out the duplicate records and involved joins. There were 3 tables involved in join. The 2 tables were 500 MB in size and around 500-600k records. The 3rd table was 40GB with 20-25 millions of records.

The select statement was fetching records from 3rd table and joining with other 2 tables.

To expedite the report processing I created an index on 4 columns, the columns of big table. A composite index.

1. Index with nologging and parallel clause. The 1st attempt failed because of temporary tablespace errors.

In the 2nd attempt, I disable the logging of the base object and then used nologging+parallel clause.

After the index creation I used dbms_stats.gather_table_stats with cascade option and degree 5. It was 6 processor machine so I followed n-1 rule.

I ran the sql statement and the query finished in 1 minute 40 seconds.

The concept is Index Fast Full Scan.