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

for program A; in order to simplify comparisons made with program B, we will
nevertheless assume a descending key.

SQL 8.9

Program A

DECLARE CURSORC CURSOR FOR
SELECT CNO, CNAME, CTYPE

FROM CUST

WHERE CCTRY = :CCTRY

DECLARE CURSORI CURSOR FOR
SELECT INO, IEUR

FROM INVOICE

WHERE IEUR > :IEUR

AND
CNO = :CNO

OPEN CURSORC
FETCH CURSORC while CCTRY = :CCTRY

OPEN CURSORI
FETCH CURSORI while IEUR > :IEUR

CLOSE CURSORI
CLOSE CURSORC

Sort the result rows by IEUR DESC

Step 1: Read the Slice (CCTRY :CCTRY) from Index (CCTRY, CNO,

CNAME, CTYPE) With a filter factor of 10% for predicate CCTRY = :CCTRY,
the slice has 0 . 1 × 1 , 000 , 000 index rows. No table access is required as we are
using a three-star index.
The two major costs in this step are now the index scan, which is as thin
as it can be, and, much more importantly, the FETCH calls; the huge number of
calls is taking 10 s!

=

Index CCTRY, CNO, CNAME, CTYPE TR = 1 TS = 10% × 1,000,000

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

LRT TR = 1 TS = 100,000

1 × 10 ms 100,000 × 0.01 ms
10ms+1s+10s=11s

Step 2: For Each CNO, Read All the Large Invoices Using Index (CNO,
IEUR DESC, INO) Now the index slice for each CNO is defined by two
columns, CNO and IEUR. When a customer doesn't have any large invoices,
the DBMS needs to do only one random touch to index (CNO, IEUR DESC,
INO). If the first index row relates to a large invoice, the DBMS must read the
next index row, and so on. Therefore, the number of random touches is 100,000