Database Reference
In-Depth Information
The result rows are obtained in the requested order (ORDER BY FNAME),
when the DBMS chooses index (LNAME, FNAME); no sort is needed. There-
fore, the DBMS will not do early materialization : OPEN CURSOR generates no
touches, but each FETCH generates touches to both the index and the table,
some of which will return a result row to the FETCH and some, the majority,
that won't.
In Figure 6.4 the first two touches (one index, one table) produce a result row
because the first Jones lives in London. The first FETCH is now satisfied, and
the program issues a second FETCH. The next Jones does not live in London,
and so does not provide a result row, so more touches to both the index and table
take place until another London Jones is found to satisfy the FETCH. Because
only 10% of the customers live in London, the average FETCH will need to
check 10 Jones entries—10 index touches and 10 table touches—before it adds
a row to the result table.
Thus, it takes on average 20
× (
10
+
10
) =
400 touches to build a full screen
with 20 customers.
Index LNAME, FNAME
TS = 20 × 10
TR = 1
TR = 20 × 10
Table CUST
TS = 0
Fetch 20 × 0.1 ms
LRT Large Result Set TR = 201 TS = 200
201 × 10 ms 200 × 0.01 ms
2s+2ms+2ms=2s
The local response time according to the QUBE is perhaps almost acceptable,
2 s. But is this really the worst input response time? The assumed filter factors,
10% for CITY = : CITY and 1% for LNAME = : LNAME, were the maximum
values . However, in this case the filter factors producing the largest result table
LNAME FNAME
CITY
T
BERN
JONA
MARIA
T
LONDON
JONES
JONES
ADAM
ADAM
T
T T T T
ZAK
JONES
T
JONI
BETTY
LONDON
1,000,000 index rows
1,000,000 table rows
Figure 6.4 Filter factor pitfall example—index entries.
Search WWH ::




Custom Search