Database Reference
In-Depth Information
TS = 1%
×
1,000,000
Index A, B, C
TR = 1
Fetch 0
×
0.1 ms
LRT Empty Result Set
TR = 1 TS = 10,000
1
×
10 ms 10,000
×
0.01 ms
10ms+100ms+0ms=110ms
Clearly, the
worst case
for this transaction is the
empty result set or any
result set that implies reading the whole index slice
. Even worse, consider the
implication of this case
not being index only
and the index
not being a cluster-
ing index
; an additional 10,000 TRs would be required, taking
nearly 2 min
to
discover that not a single row could be found!
Consider also the implication of a
thicker
index slice being used in the
matching scan process, a 20% filter factor for column A, for example, giving an
LRT of 2 s (200
,
000
×
0
.
01 ms), even with an index only scan! When the result
fits on one screen, the first star (minimize the thickness of the index slice to be
scanned) is more important than the second star (avoid a sort).
This
filter factor pitfall
may arise when the following
three conditions
are
all true:
1.
There is
no sort
in the access path.
2.
The transaction responds as soon as the
first screen is built
.
3.
All
the predicate columns
do not
participate in defining the index slice to
be scanned—in other words, they are
not all
matching columns.
All three conditions apply to this example:
ž
There is no sort because column B (the Order By column) follows the
equal predicate column A, which is the first column in the index.
ž
The transaction responds as soon as the first screen is built (20 rows).
ž
One predicate column (column C) does not participate in defining the index
slice to be scanned.
The reasoning behind these three conditions should be fully understood:
If conditions 1 and 2 are
not
true, the whole result table is always mate-
rialized either by the DBMS or by the application program (by issu-
ing FETCHes).
ž
If condition 3 is
not
true, there is no index
screening
(because all the
predicate columns are
matching
); every index row in the index slice qual-
ifies. Even if the result table is empty, the DBMS makes only one touch
to determine that this is so.
ž
FILTER FACTOR PITFALL EXAMPLE
In this section we will once more show how we may use the two estimation
techniques in the index design approach discussed in Chapters 4 and 5.
Search WWH ::
Custom Search