NO_EXPAND Hint

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 - 17:42:44 CST