Database Reference
In-Depth Information
***
LNAME
CITY
FNAME CNO
::::::::::::::
::::::::::::::
::::::::::::::
::::::::::::::
JONES LISBON MARIA 2026477
JONES LONDON JAMES 1234567
JONES LONDON MIKE 0037380
JONES LONDON MIKE 1012034
JONES MADRID TAPIO 0968431
::::::::::::::
::::::::::::::
::::::::::::::
::::::::::::::
Figure 4.2 Three-star index for CURSOR41.
SQL 4.2
DECLARE CURSOR41 CURSOR FOR
SELECT
CNO, FNAME
FROM
CUST
WHERE
LNAME = :LNAME
AND
CITY = :CITY
ORDER BY
FNAME
If there are 1000 result rows, the filter factor for the compound predicate
LNAME
:CITY is 0.1%. The scanned index slice now
consists of only 1000 rows because there are two matching predicates: columns
LNAME and CITY define the index slice (this is why the slice is shaded in
Fig. 4.2). Confirmation of the matching process will be shown by EXPLAIN:
two matching columns (MC
=
:LNAME AND CITY
=
=
2). This will take
1
×
10 ms
+
1000
×
0.1 ms
=
0.1 s
to read this index slice. The table is not accessed at all because all the required
columns have been copied into the index.
How the Stars Are Assigned
An index deserves the first star if the index rows relevant to the SELECT are next
to each other—as in Figure 4.2—or at least as close to each other as possible.
This minimizes the thickness of the index slice that must be scanned.
The second star is given if the index rows are in the right order for the
SELECT—as in Figure 4.2. This eliminates sorting .
 
Search WWH ::




Custom Search