Factors Affecting the Index Design Process - Relational Database Index Design and the Optimizers - page 93

The numbers in the candidate A selection below refer to the steps described in
Chapter 4.

CandidateA

1. Pick columns A and E.
2. Add column C.
3. No columns.
4. Add columns B and D.
Candidate A is (A, E, C, B, D) or any of the following variations:
ž (A,E,C,D,B)
ž (E,A,C,B,D)
ž (E,A,C,D,B).
All these index candidates have MC 3 with CURSOR66. The DBMS reads

three index slices (A, :E1, C), (A, :E2, C), and (A, :E3, C) without any special
tricks required. The B IN predicate would participate in screening. If four match-
ing columns were required (nine index slices), it would probably be necessary
to split the SELECT into three, each with only one IN list predicate. The first
SELECT would include predicate B = :B1, the second B = :B2, and the third
B = :B3. These three SELECTs could be combined into one cursor with UNION
ALL. The EXPLAIN for this cursor should show MC = 4 three times.

=

CandidateB
IN predicate columns should not be chosen in step 1, otherwise the sequence
requirement would be destroyed and a sort would become necessary.

FILTER FACTOR PITFALL

Throughout this topic we have focused on the worst-case scenarios that arise
when the input corresponds to the maximum filter factors. This isn't always the
case, however, as this example (SQL 6.7) will show.

SQL 6.7

SELECT B
FROM TABLE
WHERE A = :A (FF = 1%)
AND
C > :C (FF = 0...10%)
ORDER BY B
WE WANT 20 ROWS PLEASE