Database Reference
In-Depth Information
Note that with this three-star index, if the result table was empty, this would
be determined by a single touch to the index. The three-star index, by definition,
fails the third requirement of the pitfall list.
Unfortunately, a three-star index would imply an additional index because
adding a column (CITY) between LNAME and FNAME in the current index
could adversely affect existing programs. We have already discussed this problem
in Chapters 4 and 5. This is why we need to consider the cheaper alterna-
tives first.
Semifat Index (Maximum Index Screening)
Adding the predicate column CITY at the end of the existing index (LNAME,
FNAME) would eliminate most of the table touches because the optimizer should
be able to do index screening in this simple case and read the table row only
when CITY has the required value.
Index (LNAME, FNAME, CITY) passes BQ: All predicate columns are in
the index. However, it only gets one star. The appropriate index rows are not
close to each other (MC is only 1) and the index is not fat. So would this cheap
solution bring acceptable response times?
Now, the cursor with this index does fall into the filter factor pitfall cat-
egory. Consequently, we have to estimate the extreme filter factors with the
index (LNAME, FNAME, CITY). One of these will be the worst case for this
access path.
LargestResultTable(say1000rows)
FF (LNAME = :LNAME) = 1%
FF (CITY = :CITY) = 10%
FF (LNAME = :LNAME AND CITY = :CITY) = 0.1%
Every tenth index row satisfies the predicate CITY = : CITY with CITY
FF = 10%, and so it takes, on average, 10 index touches to find one result row.
TS = 20 × 10
Index LNAME, FNAME, CITY
TR = 1
Table
TR = 20
TS = 0
Fetch 20 × 0.1 ms
LRT 1000 result rows
TR=21 TS=200
21 × 10 ms 200 × 0.01 ms
210ms+2ms+2ms=214ms
EmptyResultTable
FF (LNAME = :LNAME) = 1%
FF (CITY = :CITY) = 0%
FF (LNAME = :LNAME AND CITY = :CITY) = 0%
Search WWH ::




Custom Search