NO_EXPAND Hint
Charley Hudson
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...@jda.com
ykhogaly
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...@jda.com> wrote in message
news:347EB9789951D211BD3C00600839226724E81A@a2ex...
Jonathan Lewis
That's pretty clever - how did you find it ?
When I put a search for NO_EXPAND to metalink 2.0
this one didn't show up.
When I did a search on 35934.1 I got something
completely different.
A couple of further 'seek and ye shall find' notes
includes this one: (Note: 62153.1 on avoiding
excess memory usage in parsing - how to avoid..)
Use NO_EXPAND hint. This will NOT use an index
with Oracle7 but can in Oracle8
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
ykhogaly wrote in message <82pvqq$26m$1...@inet16.us.oracle.com>...
Charley Hudson
>> The Phrase "appears to be not documented until 8i" is
NOT True. Attached is
Note which clearly establishes this fact.<<
I stand corrected for not making my statement more precise. I am able to
see references to the NO_EXPAND hint in 5 different hits that come up
under Metalink 2.0 searching on the keyword NO_EXPAND. These hits are
all on Published Notes ( 2 of them in patch notes) so I concede that
NO_EXPAND is "documented"
None of these Metalink hits corresponds to the note quoted by ykhogaly.
Thanks for quoting this note in its entirety. Can you tell me the source
of this note? (Perhaps it got lost in the move from Metalink 1.7?)
So, more precisely, I was concerned that I could not find NO_EXPAND
documented in the Oracle Documentation set that is on my Oracle 8.0.5
Enterprise Server CDROM, specifically in the Oracle Tuning Guide, where
I would expect all the supported hints to be documented. I DO find it
documented in the Oracle 8i Tuning Guide.
Charley Hudson
JDA Arthur Product Development - Ann Arbor, Mi.
mailto:charley...@jda.com <mailto:charley...@jda.com>