Database Reference
In-Depth Information
Warning
Changing the order of the index columns in an existing index is as dangerous as adding
a new column between existing index columns. In both cases an existing SELECT may
become dramatically slower because the number of matching columns may be reduced
or a sort (causing early result materialization) introduced.
CHEAPEST ADEQUATE INDEX OR BEST POSSIBLE
INDEX: EXAMPLE 2
We will once more show how we may use the two techniques in the index design
process, but this time for a slightly more difficult SELECT.
INDEX CITY—SQL 5.6A
DECLARE CURSOR56 CURSOR FOR
SELECT
CNO, FNAME
FROM
CUST
WHERE
CITY = :CITY
AND
LNAME BETWEEN :LNAME1 AND :LNAME2
ORDER BY
FNAME
INDEX LNAME, FNAME—SQL 5.6B
DECLARE CURSOR56 CURSOR FOR
SELECT
CNO, FNAME
FROM
CUST
WHERE
CITY = :CITY
AND
LNAME BETWEEN :LNAME1 AND :LNAME2
ORDER BY
FNAME
Basic Question and QUBE for the Range Transaction
For this example we will assume a maximum filter factor for CITY of 10% as
before, but now that the LNAME column is in a range predicate, we will assume
a larger filter factor for it than before, say 10% also. The maximum size of the
result table will now be assumed to be 10% of 10% of 1,000,000 which is 10,000.
Search WWH ::




Custom Search