Proactive Index Design - Relational Database Index Design and the Optimizers - page 75

Index ZIP, LNAME, FNAME, CNO TR = 1 TS = 1000

Table CUST TR = 0 TS = 0

Fetch 1000 × 0.1 ms

LRT TR = 1 TS = 1000
1 × 10 ms 1000 × 0.01 ms
10 ms + 10 ms + 100 ms = 120 ms
Example 5.3 shows just how dangerous a nonclustering index access can be.
It also shows how important the third star for the index can be.

Example5.4:ClusteringIndexwithSkip-SequentialTableAccess

SQL 5.4

SELECT STREET, NUMBER, ZIP, BORN

FROM CUST

WHERE LNAME = 'JONES'

AND
FNAME = 'ADAM'
AND
CITY = 'LONDON'

ORDER BY BORN

In Example 5.4 the query will use index (LNAME, FNAME, BORN, CITY),
which is the clustering index. How many random and sequential touches will be
required to access all the Adam Joneses who live in London?
Two matching columns will be used in the index scan. According to the data
in Figure 5.6, there will only be two touches to the table instead of four because
the CITY column will be used for screening. These two table touches will be
skip-sequential (if indeed we can use this term for a mere two table touches),
because the table rows will be in the clustering index sequence. The two rows
will not be contiguous because of the column BORN before CITY in the index.
Being skip-sequential, they are counted as random reads in the QUBE. We will
discuss the impact of ignoring the benefit of skip-sequential read in Chapter 15.

Index LNAME, FNAME, BORN, CITY TR = 1 TS = 4

Table CUST TR = 2 TS = 0

Fetch 2 × 0.1 ms

LRT TR=3 TS=4

3 × 10 ms 4 × 0.01 ms
30 ms + 0.04 ms + 0.2 ms = 30 ms

CHEAPEST ADEQUATE INDEX OR BEST POSSIBLE
INDEX: EXAMPLE 1

Having looked at a few examples of how the QUBE can be used to very easily
estimate some of the more important access methods, we will now show how we