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

Conclusion: Ideal IndexesandOneScreenperTransaction

With the highest filter factor values, the local response time is 2 s, possibly
quite acceptable.

Ideal Indexes with One Screen per Transaction
Materialized and FF Pitfall

The above estimate was made with the largest filter factors, 10 and 0.1%. Unfor-
tunately, these are not the worst input now because this case fulfils the criteria
for the filter factor (FF) pitfall (Fig. 8.10). The code of the country having only
19 large invoices is now the worst input: The whole index slice containing the
large invoices must be scanned, and the CCTRY value must be checked for
each large invoice. The filter factor for the predicate CCTRY = :CCTRY is then
about 0.1%. How long would it take now to build the single-screen response with
program B + ?

ProgramB + :MaterializeOneScreenperTransaction

Step 1: Find All the Large Invoices from Index (IEUR DESC, INO, CNO)
To find all the large invoices for the single customer country, the DBMS must
scan the whole index slice containing large invoices if the response fits on one
screen. Let us make this worst-case assumption—one screen only.

Result fits on one screen

STAR T

20 KT

20 KT

CCTRY,
CNO,
CNAME,
CTYPE

U U IEUR

DESC ,
INO,
CNO

P,C F,C

P

CNO INO CNO

CUST

INVOICE

1,000,000 rows 20,000,000 rows

Figure 8.10 Program B + with ideal indexes and filter factor pitfall.