Re: NO_EXPAND Hint

From: ykhogaly <ykhogaly_at_us.oracle.com>
Date: Thu, 9 Dec 1999 22:34:17 -0700
Message-ID: <82pvqq$26m$1@inet16.us.oracle.com>

Hi,

The Phrase "appears to be not documented until 8i" is NOT True. Attached is Note which clearly establishes this fact.

SQL Statement Hints - A Summary (7.3) See [NOTE:35934.1] for CBO issues.

Session Level:
ALTER SESSION SET optimizer_goal= rule | first_rows | all_rows | choose ;

Hints:

Hints always force the use of the cost based optimizer (Except RULE).
Use ALIASES for the tablenames in the hints.
Ensure tables are analyzed.
Syntax: /*+ HINT HINT ... */ (In PLSQL the space between the '+' and the first letter of the hint is vital so /*+ ALL_ROWS */ is fine but /*+ALL_ROWS */ will cause problems @ see [BUG:697121] )
Optimizer Mode: FIRST_ROWS, ALL_ROWS Force CBO first rows or all rows. RULE Force Rule

          ORDERED                 Access tables in the order of the FROM

clause

Sub-Queries: NO_MERGE Use this hint in a VIEW to PREVENT it being merged into the parent query. (>=7.2)

PUSH_SUBQ Causes all subqueries in a query block to be

                                  executed at the earliest possible time.
                                  Normally subqueries are executed as the
last
                                  is applied is outerjoined or remote or
joined
                                  with a merge join. (>=7.2)

          MERGE_AJ        }       Put hint in a NOT IN subquery to perform
(>=7.3)
          HASH_AJ         }       SMJ anti-join or hash anti-join. (>=7.3)
                                  Eg: SELECT .. WHERE deptno is not null
                                      AND deptno NOT IN
                                          (SELECT /*+ HASH_AJ */ deptno ...)




    - Access:


          FULL(tab)               Use FTS on tab
          CACHE(tab)              If table within
<Parameter:CACHE_SIZE_THRESHOLD>
                                  treat as if it had the CACHE option set.
                                  See <Parameter:CACHE_SIZE_THRESHOLD>. Only
                                  applies if FTS used.
          NOCACHE(tab)            Do not cache table even if it has CACHE
option
                                  set. Only relevant for FTS.
          ROWID(tab)              Access tab by ROWID directly
                                    SELECT /*+ ROWID( table ) */ ...
                                      FROM tab WHERE ROWID between '&1' and

'&2';

          CLUSTER(tab)            Use cluster scan to access 'tab'
          HASH(tab)               Use hash scan to access 'tab'
          INDEX( tab index )      Use 'index' to access 'tab'
          INDEX_ASC( tab index )  Use 'index' to access 'tab' for range
scan.
          INDEX_DESC( tab index ) (Join problems pre 7.3)
          INDEX_FFS( tab index)   Index fast full scan - rather than FTS.

          INDEX_COMBINE( tab i1.. i5 )
                                  Try to use some boolean combination of
                                  bitmap index/s i1,i2 etc

          AND_EQUAL(tab i1.. i5 ) Merge scans of 2 to 5 single column

indexes.

USE_CONCAT Use concatenation (Union All) for OR (or IN)

                                  statements. (>=7.2). See [NOTE:17214.1]
                                  (7.2 requires <Event:10078>, 7.3 no hint

req)

NO_EXPAND Do not perform OR-expansion (Ie: Do not use

                                  Concatenation).



    - Joining:


          USE_NL(tab)             Use table 'tab' as the driving table in a
                                  Nested Loops join.  If the driving row
source
                                  is a combination of tables name one of the
                                  tables in the inner join and the NL should
                                  drive off the entire row-source.
                                  Does not work unless accompanied by an
ORDERED
                                  hint.

          USE_MERGE(tab..)        Use 'tab' as the driving table in a
sort-merge
                                  join.
                                  Does not work unless accompanied by an
ORDERED
                                  hint.

          USE_HASH(tab1 tab2)     Join each specified table with another row
                                  source with a hash join. 'tab1' is joined
to
                                  previous row source using a hash join.

(>=7.3)

          STAR                    Force a star query plan if possible. A
star
                                  plan has the largest table in the query
last
                                  in the join order and joins it with a
nested
                                  loops join on a  concatenated index. The
STAR
                                  hint applies when there are at least 3
tables
                                  and the large table's concatenated index
has
                                  at least 3 columns and there are no
conflicting
                                  access or join method hints.   (>=7.3)



    - Parallel Query Option:


          PARALLEL ( table, <degree> [, <instances>] )
          NOPARALLEL

"The Views expressed here are my own and not necessarily those of Oracle Corporation"

"Charley Hudson" <charley.hudson_at_jda.com> wrote in message news:347EB9789951D211BD3C00600839226724E81A_at_a2ex... > We have "discovered" the NO_EXPAND hint, which appears to be recognized > in releases as early as 7.3.4 but not documented until 8i . Our testing

> indicates that this hint is particularly helpful to us under 8.0.x with
> queries against a single table with a single composite index on A,B,C,T
> and very long WHERE predicates of the form:
>
> WHERE (A BETWEEN 1 AND 10 OR A BETWEEN 20 AND 30 OR  . . . )
>   AND (B IN (1,3,5, . . .) )
>   AND (C IN (2,4,6, . . .) )
>   AND (T IN (3,6,9, . . .) )
>
> Without the NO_EXPAND hint, the CBO goes into a very long and very
> memory consumptive phase to come up with a plan which concatentates a
> lot of table access by rowids from index range scans. My test with 40
> index range scans in the plan required 10 minutes and 312MB of PGA just
> for the parse.
>
> With the NO_EXPAND hint, the entire query executes in seconds, using the
> plan:
>
>    2    1     BITMAP CONVERSION (TO ROWIDS)
>    3    2       BITMAP OR
>    4    3         BITMAP CONVERSION (FROM ROWIDS)
>    5    4           SORT (ORDER BY)
>    6    5             INDEX (RANGE SCAN) OF
> 'AFT_STPOS_IS_I' (UNIQUE)
>    7    3         BITMAP CONVERSION (FROM ROWIDS)
>    8    7           SORT (ORDER BY)
>    9    8             INDEX (RANGE SCAN) OF
> 'AFT_STPOS_IS_I' (UNIQUE)
>   10    3         BITMAP CONVERSION (FROM ROWIDS)
>   11   10           SORT (ORDER BY)
>   12   11             INDEX (RANGE SCAN) OF
> 'AFT_STPOS_IS_I' (UNIQUE)
>        .          .                    .
>        .          .                    .
>        .          .                    .
>      etc.
>
> When the query consists of multiple Ors an IN lists (no BETWEENs), the
> Oracle8 CBO does just great on its own, using a single INLIST ITERATOR
> with no hints required.
>
> Does anyone know more about the NO_EXPAND hint? Is it safe to use under
> 7.3.4 and 8.0.x? Is there a reason I can't find it in any Oracle
> documention prior to 8i ?
>
> Charley Hudson
> JDA Arthur Product Development - Ann Arbor, Mi.
> charley.hudson_at_jda.com
>
>

Received on Thu Dec 09 1999 - 23:34:17 CST