NO_EXPAND Hint

Introduction

In our ongoing series on Oracle hints, we'll examine the purpose and usage of the NO_EXPAND hint.   The NO_EXPAND hint in Oracle instructs the optimizer to avoid transforming a query with an OR condition or an IN list in the WHERE clause into a set of separate conditions. Normally, the optimizer might consider rewriting these conditions as a UNION ALL, in order to find a more efficient execution plan. This hint prevents the optimizer from considering such transformations.  If you are trying to encourage transformation, rather than prevent it, you may want to consider the USE_CONCAT hint. 

Example

SELECT /*+ NO_EXPAND(myTab1 myTab2) */ col1 

  FROM myTab1, myTab2

  WHERE myTab1.col1= 100

  AND Mytab2.col1 = =200

  AND Mytab2.Col2 IN ('Val1','Val2','Val3')

/

Related Hints

USE_CONCAT

NO_QUERY_TRANSFORMATION

Further Reading

• You can find more information on using the hint in the documentation

Published on 

Published 10th February 2025