SQL Processing - Relational Database Index Design and the Optimizers - page 36

SQL 3.2D

DECLARE LASTINV CURSOR FOR
SELECT INO, IDATE, IEUR
FROM INVOICE
WHERE CNO = :CNO
ORDER BY INO DESC
OPTIMIZE FOR 1 ROW

Please note that these options do not restrict the number of FETCH calls
that may be issued. SELECT TOP (n) with SQL Server and FETCH FIRST n
ROW(S) ONLY with DB2 for z/OS are provided for that purpose.

When the Access Path Is Chosen

The only additional optimizer-based question we need to understand at this stage
is the extremely important one posed in Figure 3.3. It should be clear that choos-
ing the access path every time an SQL statement is executed will consume much
more processing power than doing it perhaps only once, when the application is
developed ; the cost of the access path selection process is not insignificant for
cost-based optimizers.
Far less obvious, although sometimes far more important, is that choosing
the access path every time an SQL statement is executed, may give the optimizer
a much better chance of choosing the best access path. This is because actual
values, rather than program or host variables, are then used. WHERE SALARY
>

>

1,000,000.
The items shown in Figure 3.3 indicate how optimizers allow this choice to
be made; these again are discussed later.

:SALARY is not nearly as transparent as WHERE SALARY

Once Each time

Static SQL with bind
option REOPT(VARS)
DB2 for z/OS

Bind (Static SQL)

Cached dynamic SQL
Optimize with host variable
values of first call, reuse
access path

Uncached dynamic SQL

CPU cost of access path selection
significant with cost-based optimizers

Figure 3.3 When does the optimizer select the access path.