Database Reference
In-Depth Information
100 KT
20 KT
CCTRY,
CNO,
CNAME,
CTYPE
IEUR DESC ,
CNO,
INO
MS/HJ
P,C
P
CNO
INO
CUST
INVOICE
1,000,000 rows
20,000,000 rows
Figure 8.15 MS/HJ with ideal indexes.
in CNO sequence. Candidate B would be (CNO, IEUR DESC, INO), but then
the entire table would be involved in the merge/hash process; this would result
in scanning 1000 times as many index rows (FF 0.1%). The two candidate A
indexes are shown in Figure 8.15. Strictly speaking, IEUR is not required to be
a descending key in the ideal index; for the same reasons used throughout this
entire case study, we will nevertheless assume a descending key.
Step 1: Access Index (CCTRY, CNO, CNAME, CTYPE)
TS = 10% × 1,000,000
Index CCTRY, CNO, CNAME, CTYPE
TR = 1
LRT
TR = 1 TS = 100,000
1 × 10 ms 100,000 × 0.01 ms
10ms+1s=1s
SQL 8.16
DECLARE CURSORJ CURSOR FOR
Program C
SELECT
CNAME, CTYPE,
INO, IEUR
FROM
CUST, INVOICE
WHERE
IEUR > :IEUR
AND
CCTRY = :CCTRY
AND
CUST.CNO = INVOICE.CNO
ORDER BY
IEUR DESC
OPEN CURSORJ
FETCH CURSORJ
while
IEUR > :IEUR and
CCTRY = :CCTRY
CLOSE CURSORJ
Search WWH ::




Custom Search