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

WORST CASE SQL 7.2

SELECT CNO, FNAME, ADDRESS, PHONE

FROM CUST

WHERE LNAME = 'SMITH' FF = 1%

AND
CITY = 'LONDON' FF = 10%

ORDER BY FNAME

A fat index was duly created:

CREATE INDEX Cust X4 ON Cust (LNAME, CITY, CNO, FNAME,
ADDRESS, PHONE)

and the execution plan confirmed the access path to be index only:

Execution Plan
-----------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8

Card=5 Bytes=545)

1 0 SORT (ORDER BY) (Cost=8 Card=5 Bytes=545)

2 1 INDEX (RANGE SCAN) OF 'CUST X4' (NON-UNIQUE)

(Cost=4 Card=5 Bytes = 545)

SQL Server Example

SQL Server 2000 reports similar information about the SQL call:

Physical reads 2

Read-ahead reads 50,160

Logical reads 50,002

Elapsed time 34,057 (ms)

CPU time 1,933 (ms)

This SELECT takes 34 s and uses 1.9 s of CPU time. The number of
page requests (logical reads) is 50,002, and 50 , 160 + 2 = 50 , 162 pages are read
from disk.
The SQL call is the same one used in the Oracle example; likewise the index
used, CUST X1 (LNAME, FNAME). However, the filter factors represent the
worst input (1% for LNAME, 10% for CITY—result 1000 rows). Furthermore,
the table rows are stored in the clustered index (CNO).
With the worst input (FF for LNAME 1%), the optimizer chose a full table
scan (a good choice if the access path costs can be estimated every time—10,000
random reads would have taken much longer) when the only relevant index was
(LNAME, FNAME).
The execution plan shows that the whole clustered index (CNO) is scanned
(no Index Seek), and the result rows are sorted.