Database Reference
In-Depth Information
specifies that the table access order must be A, B, C. Replacing this hint by
LEADING would specify that the outermost table, the driving table, in a join
must be the first table in the FROM clause. The optimizer is to choose the order
of the other tables:
/*+FULL(table)*/
Choose full table scan
/*+INDEX(table index)*/
Choose index scan
/*+NO INDEX(table index)*/
Do not use specified indexes
/*+INDEX FFS*/
Choose FFS (1)
/*+USE NL(table)*/
Choose nested loop join (2)
/*+USE HASH(table1 table2) */
Choose hash join
/*+USE MERGE(table1 table2)*/
Choose sort-merge join
/*+CARDINALITY(card)*/
The size of result table (3)
1. Fast full index scan (FFS) reads all leaf pages in physical sequence,
parallelism enabled. The result is not in index key order.
2. The specified table is the inner table in a nested loop.
3. A table name can be added to the hint to specify an intermediate result.
In SQL Server 2000, the query hints are usually expressed by using the
OPTION clause, such as the following example, which means we want 20
rows please:
SELECT...FROM...WHERE...ORDER BY...OPTION(FAST 20)
or by an addition to the JOIN clause, such as a request for a hash join to
take place:
SELECT...FROM table1 INNER HASH JOIN table2 ON...
The SQL Server hints include the following; the first one specifies an index or
a set of indexes with ANDing, and the rest for specifying the join method and
table access order:
INDEX =
HASH
MERGE
LOOP
FORCE ORDER
In DB2 for z/OS, the access path hints are implemented externally to the
SQL code. The characteristics of the access path chosen by the optimizer are
stored in PLAN TABLE, which has dozens of columns. Many of these columns
can be updated to describe the alternative we would like the optimizer to choose.
The SQL call is then rebound with parameters that refer to the specified access
path. If that access path is one of the alternatives seen by the optimizer, it will be
chosen without any cost estimate being made. Thus we may, for instance, specify
another index, another join method, or a different table access order. However, if
Search WWH ::




Custom Search