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