Optimization rules for SQL queries

When SQL query optimization occurs, all of the optimization rules are applied to the condition in the query and then the predicates are reordered.

AND and OR optimization, and the reordering of predicates are applied to SQL queries as follows.

AND optimization

If a WHERE clause consists of multiple predicates that are connected by the AND operator, optimization occurs only if the same column and comparison operator are used in every predicate, and the comparison operator is LIKE or equal to (=).

The acceptable format for AND optimization is:
where column operator expr1 AND column operator expr2 AND column operator expr3...

In this WHERE clause, operator is LIKE or = only.

This format is optimized into an ALL list as follows:
where column operator ALL (expr1, expr2, expr3, ...)
Example 1
Original SQL query:
where Node like 'ibm' and Node like 'com'
Optimized query:
where Node like all('ibm','com')
Example 2
Original SQL query:
where Node like 'ibm' and Node like all('com','uk')
Optimized query:
where Node like all('ibm','com','uk')
Example 3
Original SQL query:
where Node like all('ibm','com') and Node like all('uk','london')
Optimized query:
where Node like all('ibm','com','uk','london')

OR optimization

If a WHERE clause consists of multiple predicates that are connected by the OR operator, optimization occurs only if the same column and comparison operator are used in every predicate, and the comparison operator is LIKE or equal to (=).

The acceptable format for OR optimization is:
where column operator expr1 OR column operator expr2 OR column operator expr3...

In this WHERE clause, operator is LIKE or = only.

This format is optimized into an ANY list as follows:
where column operator ANY (expr1, expr2, expr3, ...)
Example 1
Original SQL query:
where Node like 'London' or Node like 'Copenhagen'
Optimized query:
where Node like any('London', 'Copenhagen')
Example 2
Original SQL query:
where Severity = 1 or Severity = any(2,3)
Optimized query:
where Severity = any(1,2,3)
Example 3
Original SQL query:
where Severity = any(1,2) or Severity = any(3,4)
Optimized query:
where Severity = any(1,2,3,4)

Reordering of predicates

The optimizer reorders the evaluation of the predicates in a WHERE clause according to their assigned execution cost.

If the first predicate (that is, the cheapest) in an OR optimization evaluates to TRUE, the more expensive predicates (that is, any that follow) do not have to be evaluated. Similarly, in an AND optimization, if the first predicate evaluates to FALSE, the more expensive predicates do not have to be evaluated.

The assigned execution cost, from lowest to highest, is:
  1. True/False
  2. Integer comparison
  3. String comparison
  4. Integer ANY/ALL/IN
  5. String ANY/ALL/IN
  6. Subselect - that is a nested SELECT statement
Example: AND optimization (a AND b AND c)
Original SQL query:
where Summary like 'tool' and Serial in (1, 2, 3, 4, 5) and Severity > 2
Optimized reordered query:
where Severity > 2 and Summary like 'tool' and Serial in (1, 2, 3, 4, 5)
Example: OR optimization (a OR b OR c)
Original SQL query:
where Summary like 'tool' or Serial in (1, 2, 3, 4, 5) or Severity > 2
Optimized reordered query:
where Severity > 2 or Summary like 'tool' or Serial in (1, 2, 3, 4, 5)