December 4, 2009
A couple months ago an interesting/huge/confusing SQL statement appeared on the OTN forums that was experiencing performance problems when running on Oracle 8i. The SQL statement contained several very complex inline view, with inline views nested inside other inline views:
I offered the following suggestions to the person:
That is a long SQL statement. Splitting the SQL statement up into multiple views likely will not help performance, but could hurt performance.
A couple comments about the SQL statement:
* “SELECT DISTINCT” repeated in many places, including in an inline view using a GROUP BY clause. Are all of the DISTINCT clauses necessary?
* ORDER BY is frequently used in the inline views: “order by group_name” in the T11 inline view, “order by groupname” in the T6 inline view, “order by groupname” in the T3 inline view, “order by linecode” in the T5 inline view. Remove those ORDER BY clauses.
* “UNION” is used in T3 inline view – is it possible to replace that with a “UNION ALL”?
* IN clauses may be inefficient in Oracle 8i, consider switching to an EXISTS clause or transforming the IN clause to an inline view.
* Odd LIKE syntax in several places: where “groupname like ‘%’ and hub like ‘%'”, “lr.groupname like ‘%'” in T1 inline view, “lr.groupname like ‘%'” in T2 inline view.
* The table autoloadflow_lineresults is accessed many times – is that a large table? Is it possible to consolidate the queries accessing that table?
* The WHERE clauses seem to limit the usefulness of any indexes which may exist.
* It appears that you want the results from the T6 inline view (which join the T1, T2, T3, T4, and T5 inline views) to drive into the T7, T8, T9, T10, and T11 inline views – does the execution plan show that is happening?