Multiple Index Access - Relational Database Index Design and the Optimizers - page 198

SQL 10.3

SELECT B, C

FROM TX 1,000,000 rows

WHERE A = :A FF = 0 ... 0.01%

OR
B BETWEEN :B1 AND :B2 FF = 0 ... 0.05%

ORDER BY B

Even a fat index (A, B, C) would not help very much because the DBMS
would have to scan the whole index, taking one million sequential touches.
With index ORing , the following takes place:

ž Two sets of pointers are collected (1) and (2)—100 sequential touches to
A, 500 to B, making 600 in all with the worst input.
ž The pointers are sorted (3)—very fast.
ž Duplicates are eliminated (3)—very fast.
ž The qualifying table rows are read (4)—with the worst input and no
duplicates, 600 random touches; 600 × 10 ms = 6 s; this compares with
1 , 000 , 000 × 0 . 01 ms = 10 s for a scan of the whole index.
Some time ago, the programming standards at a bank ruled out the use of OR
in a WHERE clause , even though the optimizer was capable of using multiple
index access. Of course, a programmer may not be aware of the effects of non-BT
predicates, but there are many cases where a WHERE clause with multiple ANDs
and ORs is convenient and performs well enough with good indexes: no multiple
index access, no sort, index only. During the quick EXPLAIN review, SELECT
statements should be checked for multiple index access; inadequate indexes or
harmful ORs may be identified, which have to be replaced by UNION or by
splitting the cursor.

INDEX JOIN

The SQL Server optimizer is able to generate an access path called an index join ,
which differs from the implementation described above.
This is how Kevin Viers (3, p. 611) describes the SQL Server index join:
Another way of using multiple indexes on a single table is to join two or more
indexes to create a covering index. In case you don't remember, a covering
index is an index that contains all of the columns required for a given query.
Consider the following example:

SELECT OrderDate, ShippedDate, count(*)
FROM Orders
GROUP BY OrderDate, ShippedDate