Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: NO_EXPAND Hint

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:

          ORDERED                 Access tables in the order of the FROM
clause

          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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US