Optimizers Are Not Perfect - Relational Database Index Design and the Optimizers - page 246

SQL 14.1

DECLARE CURSOR141 CURSOR FOR
SELECT LNAME, FNAME, CNO
FROM CUST
WHERE (LNAME = :LNAMEPREV
AND
CNO > :CNOPREV)
OR
(LNAME > :LNAMEPREV
AND
LNAME <= :LNAMEMAX)
ORDER BY LNAME, CNO
WE WANT 20 ROWS PLEASE

In the first transaction 0, in the following transactions the last CNO value
shown:

LNAMEMAX
JONZZZ...

When index (LNAME, CNO, FNAME) is available, the best access path is nat-
urally MC 2, index only, no sort. The number of touches per transaction then

is minimal, TR

=

= 1, TS = 19 for a browsing transaction displaying 20 rows

per screen.
Unfortunately, the WHERE clause has no BT predicates. If the optimizer
cannot remove the OR by rewriting, it must choose between a full index scan
(MC = 0 ) and multiple index access . In the latter case, if the DBMS collects
only pointers from the index slices, the access path will not be index only.
Furthermore, the whole result table will be materialized because the sort of the
pointers implies a sort for the ORDER BY. If the result table has 1000 rows,
this means 1000 unnecessary random touches to the table. Exercise 14.1, at the
end of this chapter, considers rewriting SQL 14.1 to eliminate the OR.
The next WHERE clause is not as bad as the one shown in SQL 14.1.
Predicates P1 and P2 are BT, so the access path at least has MC = 2, assuming
predicates P1 and P2 are not difficult.

WHERE P1 AND P2 AND (P3 OR P4).

How can we help the optimizer if it does not see the best alternative? Unfor-
tunately, the SQL statement would have to be reformulated. It may even be
necessary to split a complex cursor into several cursors. Optimizers are learning
to handle transformations such as the following:

COLX = :hv1 OR COLX = :hv2 into

COLX IN (:hv1, :hv2)

Splitting a cursor, however, is a much more difficult task because application
code is required between the SQL calls.