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

FF(CCTRY = :CCTRY)
= 10%

STAR T

100 KT

P,C

CNO CCTRY

2 MT

100 KT

P F,C

INO CNO

CUST

1,000,000 rows

2 MT

INVOICE

20 invoices
per average customer

20,000,000 rows

Figure 8.4 Program A with current indexes.

Index CCTRY TR = 1 TS = 10% × 1,000,000

Table CUST TR = 100,000 TS = 0

Fetch 10% × 1,000,000 = 100,000 × 0.1 ms

LRT TR = 100,001 TS = 100,000

100,001 × 10 ms 100,000 × 0.01 ms
1000s+1s+10s=1000 s

According to the QUBE, this step contributes 1000 s to the response time!
The large number of TRs for the customer table occurs because the index CCTRY
is not the clustering index.
So much for using the “obvious” access path. With a filter factor of 10%,
100,000 TRs (17 min) is clearly not an option. Even a filter factor of 1% would
require 10,000 TRs (1.7 min). The alternative, a full table scan, would only take

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

A filter factor of 0.1% with the index scan would require 1000 TRs, tak-
ing the same length of time as the table scan. This is, of course, because one
TR, according to the QUBE, takes 1000 times longer than one TS. A full table
scan is faster than a nonfat, nonclustered index scan for filter factors greater
than 0.1%. We must be very careful, however, when making sweeping state-
ments such as this, based solely on figures produced by the QUBE. It should
also be understood that the CPU time used would be very much greater with the
table scan. Nevertheless, it should be appreciated that very low filter factors are
required these days for nonfat, nonclustered index scans to be preferred to table
scans .