Hint Specifying Not to Expand Subqueries

Function

When the database optimizes the query logic, some subqueries can be promoted to the upper layer to avoid nested execution. However, for some subqueries that have a low selection rate and can use indexes to filter access pages, nested execution does not cause too much performance deterioration, while after the promotion, the query search scope is expanded, which may cause performance deterioration. In this case, you can use the no_expand hint for debugging. This hint is not recommended in most cases.

Syntax

no_expand

Examples

Normal query execution:

explain select * from t1 where t1.a in (select t2.a from t2);

Plan:

After no_expand is added:

explain select * from t1 where t1.a in (select /*+ no_expand*/ t2.a from t2);

Plan: