1.定义

By default, if Oracle cannot unnest a subquery it postpones executes of that subquery to the end of the execution plan.

Historically the push_subq hint told Oracle to run any outstanding subqueries at the earliest possible moment. But in 10g this changes (for the better). Imagine I have two subqueries in the query; it is quite possible that the optimum execution plan is to run one early and the other late, but the original implementation of push_subq is an ‘all or nothing’ implementation. 10g allows you to be selective about which subqueries should be pushed.

hint：push_subq/no_push_subq

2.场景

个人认为这个查询转换主要用于下列场景：

1) 子查询带有聚合函数的(MAX/MIN/SUM/...)

select /*+qb_name(nb)*/ * from lxy where object_id=(select /*+qb_name(sb) */ min(object_id) from lxy_1) or object_type='TABLE';

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 89 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | LXY | 1 | 89 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 5 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| I2 | 1 | 5 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - NB / LXY@NB
2 - NB / LXY@NB
3 - SB
4 - SB / LXY_1@SB
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SB" "LXY_1"@"SB" ("LXY_1"."OBJECT_ID"))
PUSH_SUBQ(@"SB")
INDEX_RS_ASC(@"NB" "LXY"@"NB" ("LXY"."OBJECT_ID"))
OUTLINE(@"NB")
OUTLINE(@"SB")
OUTLINE_LEAF(@"NB")
OUTLINE_LEAF(@"SB")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE')
2 - access("OBJECT_ID"= (SELECT /*+ QB_NAME ("SB") */ MIN("OBJECT_ID")
FROM "LXY_1" "LXY_1"))

这里清楚的看到CBO对查询块SB进行了子查询推进,所以优先执行了子查询(并不是每种情况都是优先执行子查询的)。

2)有多个子查询

这种场景可以选择哪个子查询被优先执行，比如有某个子查询可以过滤掉大量的数据。就可以优先执行。

3)多表关联附带子查询

对于未能子查询展开的sql,通常子查询都是在最后一步执行，即多表关联完后才去执行子查询，此时如果子查询可以过滤大量数据即可减少后续连接的成本，子查询推进可以让子查询先关联后再与后续的表进行连接。

ITPub博客

Push Subquery