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

In this SELECT statement, we aren't interested in the whole result set as
we may deduce from the WE WANT n ROWS PLEASE clause. When 20 rows
have been FETCHed, the screen is presented to the user regardless of the total
size of the result set.
To explain the performance implications of this with regard to the filter
factors involved, we will compare QUBEs with two filter factor extremes; the
first provides the smallest result set possible, 0 rows; the second a fairly large
result set (1000 rows). Figure 6.2 shows both cases, using index (A, B, C).
Note that each case has the same filter factor for the matching index column
A. The difference in the size of the result set is entirely determined by col-
umn C.
Let's first consider the second case, the large result set, which normally
gives rise to the worst performance. A matching index scan (1 MC, index only)
will access an index slice of 20

×

10 rows because to provide each row in the
result set, 10 index rows need to be examined (column C FF 10%).

TS = 20 × 10 Index A, B, C TR = 1

Fetch 20 × 0.1 ms

LRT Large Result Set TR = 1 TS = 200

1 × 10 ms 200 × 0.01 ms
10ms+2ms+2ms=14ms

In the first case, no rows satisfy the column C predicate and so the filter
factor is 0%. Of course, the DBMS doesn't know this until every single row in
the index slice, 1% of 1,000,000, is examined.

No sort
Max 20 x FETCH

A, B,C

1,000,000 rows

FF ( A = :A) = 1%

FF ( C > :C) = 10%

FF ( C > :C) = 0%

Result table = 1,000 rows
LRT = 1 x 10 ms
+ 200 x 0.01 ms
+ 20 x 0.1 ms
= 14 ms

Result table = 0 rows
LRT = 1 x 10 ms
+ 10,000 x 0.01 ms
+ 0 x 0.1 ms
= 110 ms

Figure 6.2 Filter factor pitfall.