Indexing for Table Joins - Relational Database Index Design and the Optimizers - page 156

Index IEUR DESC, INO, CNO TR = 1 TS = 0.1% × 20,000,000

Fetch 20,000 × 0.1 ms

LRT TR = 1 TS = 20,000
1 × 10 ms 20,000 × 0.01 ms
10ms+0.2s+2s=2s
We have now lost the advantage of limiting the result size; fortunately the damage
is not too great.

Step 2: Read One Index Row (CCTRY, CNO, CNAME, CTYPE) for Each
Large Invoice This step requires a huge number of random touches to the
index because every large invoice has to be checked:

Index CCTRY, CNO, CNAME, CTYPE TR = 20,000

Fetch 20,000 × 0.1 ms

LRT TR = 20,000

20,000 × 10 ms
=200s+2s=202s

Local Response Time

2s + 202 s = 3 . 5min

Conclusion: Ideal IndexesandOneScreenperTransactionwithFF
Pitfall
This is a bitter disappointment; even with the promising program B

the local
response time is 3.5 min when the response fits on one screen . This is in marked
contrast to the response time for the first screen of a multiscreen result ,which
was only 2 s. Our previous message, about the benefit of using an ideal index
being minimized because of the huge number of TRs to the inner table index,
has come back to haunt us!

+

ProgramC :MaterializeOneScreenperTransaction

The modifications needed to make program C materialize only one screen per
transaction are the same as those made for program B

+

. The program shown in
SQL 8.13 produces the first screen. The local response times will be the same
as for program B + , good with large filter factors, very poor with the low filter
factor worst case. The savings to be made by reducing the number of FETCH
calls is of minimal benefit in the latter case.

+

BASIC JOIN QUESTION (BJQ)

This simple case study has shown that even the ideal indexes for the best table
access order might result in unacceptable response times. The main issue is the