Push Subquery

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/...)

1. 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博客 ” ，链接：http://blog.itpub.net/31347199/viewspace-2121461/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31347199/viewspace-2121461/