Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: NO_EXPAND Hint
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 FROMclause
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 ...)'&2';
- 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
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 columnindexes.
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 hintreq)
NO_EXPAND Do not perform OR-expansion (Ie: Do not use
Concatenation).(>=7.3)
- 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.
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
![]() |
![]() |