Hints Specifying Not to Expand Subqueries

Description

When the database optimizes the query logic, some subqueries can be pulled up to the upper layer to avoid nested execution. However, for some subqueries that have a low selectivity and can use indexes to filter pages, nested execution does not cause too much performance deterioration, while after the pull-up, 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)] 

Parameters

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