To make things a little more interesting, I incorporated some ideas from Breitling's "Tuning by Cardinality Feedback." Here is why I was hung up about the "First K Rows" part - as you can see, the estimates are horrid for the query without the distinct - I have to question where such a drastically low cost came from:
DECODE(A.SQL ID OPERATION OPTIONS OBJECT_NAME ACTUAL_ROWS ESTIMATED_ROWS ------------ -- ------------ --------------- ------------------ ----------- -------------- DISTINCT 1 HASH UNIQUE 99217 403084 DISTINCT 2 HASH JOIN 4766142 403084 DISTINCT 3 INDEX FULL SCAN PK_TABLE_A 96 96 DISTINCT 4 HASH JOIN 4812046 406680 DISTINCT 5 HASH JOIN 355265 52851 DISTINCT 6 INDEX FAST FULL SCAN TABLE_D_VIEW_INDEX 1118359 22433 DISTINCT 7 INDEX FAST FULL SCAN TABLE_C_KEY2_INDEX 231499 231436 DISTINCT 8 TABLE ACCESS FULL TABLE_B 763001 755874
DECODE(A.SQL ID OPERATION OPTIONS OBJECT_NAME ACTUAL_ROWS ESTIMATED_ROWS ------------ -- ------------ --------------- ------------------ ----------- -------------- NON DISTINCT 1 NESTED LOOPS 4766142 10 NON DISTINCT 2 NESTED LOOPS 4812046 11 NON DISTINCT 3 NESTED LOOPS 355265 2 NON DISTINCT 4 INDEX FAST FULL SCAN TABLE_D_KEY_INDEX 1118359 22433 NON DISTINCT 5 INDEX RANGE SCAN TABLE_C_KEY2_INDEX 355265 2 NON DISTINCT 6 TABLE ACCESS BY INDEX ROWID TABLE_B 4812046 6 NON DISTINCT 7 INDEX RANGE SCAN PK_TABLE_B 4812046 6 NON DISTINCT 8 INDEX UNIQUE SCAN PK_TABLE_A 4766142 1
-- Charles Schultz