I was having some performance issues with an Oracle query, so I downloaded a trial of the Quest SQL Optimizer for Oracle, which made some changes that dramatically improved the query's performance. I'm not exactly sure why the recommended query had such an improvement; can anyone provide an explanation?
Before:
SELECT t1.version_id,
t1.id,
t2.field1,
t3.person_id,
t2.id
FROM table1 t1,
table2 t2,
table3 t3
WHERE t1.id = t2.id
AND t1.version_id = t2.version_id
AND t2.id = 123
AND t1.version_id = t3.version_id
AND t1.VERSION_NAME <> 'AA'
order by t1.id
Plan Cost: 831
Elapsed Time: 00:00:21.40
Number of Records: 40,717
After:
SELECT /*+ USE_NL_WITH_INDEX(t1) */ t1.version_id,
t1.id,
t2.field1,
t3.person_id,
t2.id
FROM table2 t2,
table3 t3,
table1 t1
WHERE t1.id = t2.id + 0
AND t1.version_id = t2.version_id + 0
AND t2.id = 123
AND t1.version_id = t3.version_id + 0
AND t1.VERSION_NAME || '' <> 'AA'
AND t3.version_id = t2.version_id + 0
order by t1.id
Plan Cost: 686
Elapsed Time: 00:00:00.95
Number of Records: 40,717
Questions:
Why does re-arranging the order of the tables in the FROM clause help?
Why does adding + 0 to the WHERE clause comparisons help?
Why does || '' <> 'AA' in the WHERE clause VERSION_NAME comparison help? Is this a more efficient way of handling possible nulls on this column?
