Your SQL query may use conditions to limit the Query Processor’s search so that it only searches for data that meets certain criteria. The conditions affect the way the query is processed, and so for the first stage of the optimization algorithm, the Query Processor builds a list of sub-clauses (conditions that are separated by AND operators).
Example:
In the following condition, there are two sub-clauses:
( Id = 10 OR Id =12 ) AND Value >= 75.0
The sub-clauses are:
- ( Id = 10 OR Id =12 )
- Value >= 75.0
By creating the list of sub-clauses, the Query Processor can avoid any performance inhibiting issues related to the order of the sub-clauses. This is because the sub-clause list allows the Query Processor to use the sub-clauses in any order without affecting the result.
To build the sub-clause list, the Query Processor examines the join specifiers and the WHERE clause in your query:
- If your query’s join clause uses a NATURAL JOIN of the form ‘Table A NATURAL JOIN Table B’, a sub-clause is added to the sub-clause list. The sub-clause that is added takes the form TableA.Column=TableB.Column and there is one sub-clause added for each column that is present in both Table A and Table B.
- If your query’s join clause uses an ON condition, the Query Processor splits the condition into AND-separated sub-clauses, with each sub-clause added to the sub-clause list.
- If your query’s join clause uses USING (ColumnList), the Query Processor will add a sub-clause of the form TableA.Column = TableB.Column to the sub-clause list. It will add one sub-clause for each column in the defined ColumnList.
- The Query Processor will also split the WHERE clause in your query into AND-separated sub-clauses. Each of these AND-separated clauses are added to the sub-clause list.
When the Query Processor has built the sub-clause list, it may transform some of the list entries into one or more equivalent sub-clauses. This process does not affect the syntax of your SQL query, it is simply used to make it easier for the Query Processor to calculate access paths.
The Query Processor will also collate comparisons in your query—it disregards any redundant comparisons, for example, if a sub-clause has >10 and >11, >10 will be ignored as it is made redundant by the >11 . Again, this does not affect the syntax of your query, but allows the Query Processor to handle your query more efficiently.
When the sub-clause list has been built, the Query Processor will categorize the sub-clauses to improve efficiency (see Determine the Sub-Clause Types).
Further Information