I have a
MERGE statement that took forever to execute the select as the table it is running from (
SUB_DATA) is rather big (read 1.4 Tb, 700 million rows). My
SELECT looks like this.
SELECT MIN(TRUNC(DATETIME,'DD')) AS FIRST_ACTIVITY, MAX(TRUNC(DATETIME,'DD')) AS LAST_ACTIVITY, NVL(PHONE_NUMBER, '-1') AS PHONE_NUMBER, MAX(NVL(PHONE_ID, '-1')) KEEP (DENSE_RANK LAST ORDER BY DATETIME) AS LAST_PHONE_ID, MAX(TYPE_ID) KEEP (DENSE_RANK LAST ORDER BY DATETIME) AS TYPE_ID FROM SUB_DATA AD, SUB_PROF ICP WHERE DATETIME BETWEEN minDate AND maxDate AND PROFILE_ID = ICP.ICP_SEQID AND REF_RT_ID in (13, 63) AND DURATION > 3 AND PHONE_NUMBER like '123%' GROUP BY NVL(PHONE_NUMBER, '-1')
There are two
INDEXES on the table, one on
PHONE_NUMBER and one on another column not used here.
I struggled to get the query to run faster and one of the more senior guys at work suggested I force a
FULL TABLE SCAN. I did this and it worked wonders.
Even though it worked, it goes against the general concept that full table scans are bad.
Could someone please explain why this is the case?