ANDed search has the form
condition2, as in this example:
WHERE column1 = 'x' AND column2 = 'y'
Here, the optimizer's decision process can be described as follows:
If (neither condition is indexed) use sequential scan.
Otherwise, if (one condition has better join type) then pick a driver based on join type (see Section 188.8.131.52, “Determining the Join Type”).
Otherwise, since (both conditions are indexed and have equal join type) pick a driver based on the first index that was created.
The optimizer can also choose to perform an
index_merge index intersection, as
Section 184.108.40.206.2, “Index Merge Optimizer”.
Here's an example:
CREATE TABLE Table1 (s1 INT, s2 INT); CREATE INDEX Index1 ON Table1 (s2); CREATE INDEX Index2 ON Table1 (s1); ... SELECT * FROM Table1 WHERE s1 = 5 AND s2 = 5;
When choosing a strategy to solve this query, the optimizer picks s2 = 5 as the driver because the index for s2 was created first. Regard this as an accidental effect rather than a rule, it could change at any moment.
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices