Hints 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

1

no_expand[(@queryblock)] 

Parameter Description

For details about @queryblock, see Hint Specifying the Query Block Where the Hint Is Located. @queryblock can be omitted, indicating that the hint takes effect in the current query block. If it is not specified, no_expand does not have parentheses (()).

Example

Normal query execution:

1

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

Plan

After no_expand is added:

1

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

Plan