Database Reference
In-Depth Information
SEX = 'F . The appropriate SELECT must be chosen by the application
program. Furthermore, the INIT.ORA parameter must be either EXACT
or SIMILAR.
ž In some environments, dynamic SQL may have to be used, with SQL
caching disabled.
Optimize every time should not be confused with optimize the first time. Ora-
cle 9i makes the cost estimate the first time an SQL call is executed and then
stores the SQL and the execution plan for reuse; this is called bind variable peek-
ing . DB2 for z/OS V8 provides the same facility by means of REOPT(FIRST)
for dynamic SQL. With these options, if the first execution of SELECT AVG
(WEIGHT) FROM SUMO WHERE SEX
=
:SEX refers to men, the optimizer
will always use a full table scan.
When filter factors are estimated with values, either with constants or values
moved to a variable, the optimizer, of course, needs adequate information about
the column (or column group) value distributions: a histogram or TOP/BOTTOM
N. How else would the optimizer know, for instance, that 99.9% of Sumo
wrestlers are men?
AccessPathHints
Access path hints provide a more effective solution than optimize every time when
the best access path is independent of the input—unaffected by, for example,
skewed distributions. The hint informs the optimizer that the indicated access
path is the cheapest one. If the optimizer is able to generate this access path, it
will not even estimate the costs.
There are three kinds of hints:
1. Specific (the whole access path is specified)
2. Restrictive (some alternatives are ruled out)
3. Informative (the optimizer is given information that enables better cost
estimates)
Oracle now has more than 100 different hints and they are widely used,
partly for historical reasons. SQL Server 2000 has a limited number of hints
(join method, choose index, etc.). DB2 for z/OS has the facility described below
for specifying any access path the optimizer is able to build. DB2 for LUW V8
has no hint facility.
To provide some understanding of the scope of access path hints, we will
show a few simple Oracle examples, but please understand that this is but a
small sample:
SELECT /*+ ORDERED */ C1,C2,C3
FROM A,B,C
WHERE ...
Search WWH ::




Custom Search