The Logical Operators in Oracle are AND, OR, and NOT. The logical operators are each teated differently with respect to the use of indexes and hash clusters.
Any predicate within a NOT operator will not use an index or a hash cluster scan. The one exception to this is bitmap indexes.
The AND operator is the best for using indexes and hash cluster scans. If your WHERE clause contains only AND operators, then any one or more of the predicates could use and index.
Consider the SQL:
SELECT * FROM my_table WHERE col1 = :x OR col2 = :yIf both col1 and col2 are indexed, then Oracle may choose to expand the SQL internally into:
SELECT * FROM my_table WHERE col1 = :x UNION SELECT * FROM my_table WHERE col2 = :yIf you run a SQL with OR predicates through Explain Plan, you may see that it is re-writing it as a UNION. If not, you can force this behaviour with the USE_CONCAT hint.
Expansion into UNIONs is the only way to use indexes or hash cluster scans with OR predicates. The one exception to this is bitmap indexes.
Don't do this
SELECT * FROM my_table WHERE col1 = :a OR col1 = :b
Do this instead:
SELECT * FROM my_table WHERE col1 IN (:a, :b)
In the first example, Oracle will expand it into a UNION which results in a sort. The second example can be performed in a single scan (Oracle calls this Inlist Iterator) and avoides the sort; much quicker on a large SELECT.