Reactive Index Design - Relational Database Index Design and the Optimizers - page 126

The execution plan shows that index (LNAME, FNAME) was used; a slice
was read (RANGE SCAN), not the whole index. The table was accessed and
then, the only surprise, the result was sorted. The sort is not a problem, how-
ever, because the result is only 5 rows. This happens to be exactly what the
optimizer had assumed (CARD = 5) because the input was chosen to represent
the average case.
The measured times are now easy to understand: 1000 sequential touches to
the index cause a relatively small number of disk I/Os; if there are 40 index rows
per leaf page, the number of leaf pages read sequentially will be 25. This should
take only a few milliseconds. The average time per random read seems to be about
7 ms, which is quite a short time. Oracle may have used data block prefetching
to do random reads in parallel (an unofficial access path, not reported in the
execution plan). That would explain the surprising sort in the execution plan.
The QUBE for the current index with the above input is

1001 × 10 ms + 1000 × 0 . 01 ms + 5 × 0 . 1ms = 10 s : measured 7 s

The reason for the long response time, 7 s, can now be ascertained by compar-
ing the SELECT statement with the index: Column CITY is not in the index.
The index is not even semifat! As the filter factor for predicate LNAME

=
'LNAME287' is 0.1%, Oracle must do 1000 random touches to table CUST to
evaluate predicate CITY

=

'TRURO' in order to find the required 5 rows. The
solution is obvious: to at least add CITY to the index, thereby making it semifat
to eliminate the table touches.
Before we rush ahead and devise a solution, we should reflect that 5 rows
is actually a very small result set; consequently, this may not indeed be the
worst case. Any solution considered should also be adequate for the worst case;
a semifat index may not be adequate for the worst input. The filter factors for
this are LNAME 1% and CITY 10%.
The QUBE for the current index and the worst input is

10 , 001 × 10 ms + 10 , 000 × 0 . 01 ms + 1000 × 0 . 1ms = 100 s : measured 72 s

while for a semifat index with the above input, the QUBE would be

6 × 10 ms + 1000 × 0 . 01 ms + 5 × 0 . 1ms = 0 . 07 s

and for a semifat index with the worst input, the QUBE would be

1001 × 10 ms + 1000 × 0 . 01 ms + 1000 × 0 . 1ms = 10 s

It was certainly worthwhile checking against the worst case; a semifat index
would have been totally unacceptable. The solution must be a fat index , with a
QUBE ( worst input) of

1 × 10 ms + 1000 × 0 . 01 ms + 1000 × 0 . 1ms = 0 . 1s