A single-table query including or clauses is a union of more than one query. Although some rows may match more than one of the conditions, each row must be returned only once. Depending on indexes and query clauses, or queries can be resolved by one of these methods:
If any of the clauses linked by or is not indexed, the query must use a table scan. If there is an index on type, but no index on advance, this query performs a table scan:
select title_id, price from titles where type = "business" or advance > 10000
If there is a possibility that one or more of the or clauses could match values in the same row, the query is resolved using the OR strategy, also known as using a dynamic index. The OR strategy selects the row IDs for matching rows into a worktable, and sorts the worktable to remove duplicate row IDs. For example, there can be rows for which both of these conditions are true:
select title_id from titles where pub_id = "P076" or type > "business"
If there is an index on pub_id, and another on type, the OR strategy can be used.
See “Dynamic index (OR strategy)” for more information.
The OR Strategy (multiple matching
index scans) is only considered for equality predicates. It is disqualified
for range predicates even if meeting other conditions. As an example,
when a select statement contains the following:
where bar between 1 and 5
or bar between 10 and 15
This will not be considered for the OR Strategy.
If there is no possibility that the or clauses can select the same row, the query can be resolved with multiple matching index scans, also known as the special OR strategy. The special OR strategy does not require a worktable and sort. The or clauses in this query cannot select the same row twice:
select title_id, price from titles where pub_id = "P076" or pub_id = "P087"
With an index on pub_id, this query can be resolved using two matching index scans.
See “Multiple matching index scans (special OR strategy)” for more information.
The costs of index access for each or clause are added together, and the cost of the sort, if required. If sum of these costs is greater than a table scan, the table scan is chosen. For example, this query uses a table scan if the total cost of all of the indexed scans on pub_id is greater than the table scan:
select title_id, price
from titles
where pub_id in ("P095", "P099", "P128", "P220", "P411", "P445", "P580", "P988")
If the query contains additional search arguments on indexed columns, predicate transformation may add search arguments that can be optimized, adding alternative optimization options. The cost of using all alternative access methods is compared, and the cheapest alternative is selected. This query contains a search argument on type as well as clauses linked with or:
select title_id, type, price from titles where type = "business" and (pub_id = "P076" or pubdate > "12/1/93")
With a separate index on each search argument, the optimizer uses the least expensive access method:
The index on type
The OR strategy on pub_id and pubdate
