NO_QUERY_TRANSFORMATION Hint

Introduction and Warning

In our ongoing series on Oracle hints, we'll examine the purpose and usage of the NO_QUERY_TRANSFORMATION hint. Before discussing how to use the parallel NO_QUERY_TRANSFORMATION hint, it is important to note, that this hint, a bit like the parallel hints should be used sparingly.  Most hints tend to affect a single pathway through the optimizer.  For example, the INDEX hint will tell the optimizer, use this index instead of a different index or no index at all.  Or, the USE_NL will tell the optimizer to use a nested loop, rather than, in most cases, a HASH Join.  However, the NO_QUERY_TRANSFORMATION can be much more impacting, especially on complex queries as it can change multiple pathways.  To fully understand what this hint does, we need to know what a query transformation is.

What is a Query transformation

Oracle's optimizer may automatically transform SQL queries before execution through a process called query transformation. The optimizer will analyse the query for possible re-writes in an attempt to improve performance, without changing the query results. Common examples of query transformation include

• View Merging

• Sub-Query Un-Nesting

• OR-Expansion (converting OR conditions to UNION ALL)

• Materialized View Rewriting

• Star Transformation

In most cases, these transformation are beneficial, but sometimes they can cause unexpected performance problems. This is where the NO_QUERY_TRANSFORMATION hint can be useful as it will turn off these transformations.

IMPORTANT NOTE

It is worth mentioning that the above transformations are not a exhaustive list.  There are many transformations the optimizer can and does use.  Furthermore, not all transformations are turned off by the NO_QUERY_TRANSFORMATION hint. There is no definitive list of transformation, nor is there a definaitve list of what is and is not turned off by this hint.  The documentation quotes "The NO_QUERY_TRANSFORMATION hint instructs the optimizer to skip all query transformations, including but not limited to OR-expansion, view merging, subquery unnesting, star transformation, and materialized"

Example

The following SQL statement

SELECT emp_no, name 

  FROM empTab

  WHERE deptId IN (SELECT deptId FROM deptTab WHERE locId = 'London')

/

May be re-written by the optimizer, if the optimizer chooses to transform it 

SELECT emp_no, name

  FROM empTab e

  JOIN deptTab d ON e.deptId = d.deptId

  WHERE d.locId = 'London'

/

Placing the NO_QUERY_TRANSFORMATION hint into the query will prevent the transformation

SELECT /*+ NO_QUERY_TRANSFORMATION */ emp_no, name 

  FROM empTab

  WHERE deptId IN (SELECT deptId FROM deptTab WHERE locId = 'London')

/

IMPORTANT NOTE

In this simplified example above, its easy to see how the transformation works and how it is turned off.  But remember the NO_QUERY_TRANSFORMATION hint has the potential to turn off multiple transformations.  So on a complex query this may turn off optimisations that are beneficial as well as those that are not beneficial.  For example, it may turn off view merging and sub-query un-nesting if both transformations were occurring in the original plan.   In most cases, you would be better off using a different hint that affects the specific transformation you are targeting.  For example, NO_UNEST for sub-query un-nesting.  

You can see how powerful and potentially destructive the NO_QUERY_ TRANSFORMAION hint can be. Use with caution.

Further Reading

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

Published on 

Published 10th February 2025