no_unnest,push_subq用法小试

create table t1 as select * from dba_objects;

Table created.

create table t2 as select * from dba_objects;

Table created.

create table t3 as select * from dba_objects;

Table created.

create index w_1 on t1(object_id);

Index created.

create index w_2 on t2(object_id);

Index created.

create index w_3 on t3(object_id);

Index created.

exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

exec dbms_stats.gather_table_stats(user,'t2');

PL/SQL procedure successfully completed.

exec dbms_stats.gather_table_stats(user,'t3');

PL/SQL procedure successfully completed.

explain plan for
  2  select t1.object_id,t1.object_name ,t2.object_type
  3   from t1 ,t2 where t1.object_id=t2.object_id and t2.object_type='TABLE'
  4   and exists ( select 1 from t3 where t3.object_name='WXH' AND t3.object_id=t1.object_id);

Explained.

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3087771463

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |     1 |    61 |    79   (2)| 00:00:01 |
|   1 |  NESTED LOOPS                  |      |       |       |            |          |
|   2 |   NESTED LOOPS                 |      |     1 |    61 |    79   (2)| 00:00:01 |
|   3 |    NESTED LOOPS                |      |     1 |    48 |    77   (2)| 00:00:01 |
|   4 |     SORT UNIQUE                |      |     1 |    24 |    74   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL         | T3   |     1 |    24 |    74   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T1   |     1 |    24 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | W_1  |     1 |       |     1   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN            | W_2  |     1 |       |     1   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS BY INDEX ROWID  | T2   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("T3"."OBJECT_NAME"='WXH')
   7 - access("T3"."OBJECT_ID"="T1"."OBJECT_ID")
   8 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   9 - filter("T2"."OBJECT_TYPE"='TABLE')

看到ORACLE把子查询展开和t3进行了join。看看使用no_unnest提示的情况。

explain plan for
  2  select t1.object_id,t1.object_name ,t2.object_type
  3   from t1 ,t2 where t1.object_id=t2.object_id and t2.object_type='TABLE'
  4   and exists ( select /*+ no_unnest */1 from t3 where t3.object_name='WXH' AND t3.object_id=t1.object_id);

Explained.

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2811354197

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    37 |  3609   (1)| 00:00:44 |
|*  1 |  FILTER                      |      |       |       |            |          |
|*  2 |   HASH JOIN                  |      |  3458 |   124K|   149   (1)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T2   |  3474 | 45162 |    74   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL         | T1   | 19046 |   446K|    74   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T3   |     1 |    24 |     2   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN          | W_3  |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T3" "T3" WHERE
              "T3"."OBJECT_ID"=:B1 AND "T3"."OBJECT_NAME"='WXH'))
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - filter("T2"."OBJECT_TYPE"='TABLE')
   5 - filter("T3"."OBJECT_NAME"='WXH')
   6 - access("T3"."OBJECT_ID"=:B1)

使用no_unnest提示后，子查询将不被展开，T1.T2完成join后，再通过filter操作进行过滤。

再看看push_subq来将子查询强制先进行join-filter。

explain plan for
  2  select /*+ push_subq(@tmp)  */t1.object_id,t1.object_name ,t2.object_type
  3   from t1 ,t2 where t1.object_id=t2.object_id and t2.object_type='TABLE'
  4   and exists ( select /*+ qb_name(tmp) no_unnest */1 from t3 where t3.object_name='WXH' AND t3.object_id=t1.object_id);

Explained.

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2880557960

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |   948 | 35076 |   151   (1)| 00:00:02 |
|*  1 |  HASH JOIN                    |      |   948 | 35076 |   149   (1)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL           | T1   |   952 | 22848 |    74   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T3   |     1 |    24 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | W_3  |     1 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL           | T2   |  3474 | 45162 |    74   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST QB_NAME ("TMP") */ 0
              FROM "T3" "T3" WHERE "T3"."OBJECT_ID"=:B1 AND "T3"."OBJECT_NAME"='WXH'))
   3 - filter("T3"."OBJECT_NAME"='WXH')
   4 - access("T3"."OBJECT_ID"=:B1)
   5 - filter("T2"."OBJECT_TYPE"='TABLE')

这里的执行计划显示的不够专业。可能是ORACLE怕格式不好看，FILTER操作被隐藏了。

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |   948 | 35076 |   151   (1)| 00:00:02 |
|*  1 | HASH JOIN                     |      |   948 | 35076 |   149   (1)| 00:00:02 |
|     |  FILTER                       |      |       |       |    00      | 00:00:01 |
|*  2 |   TABLE ACCESS FULL           | T1   |   952 | 22848 |    74   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T3   |     1 |    24 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | W_3  |     1 |       |     1   (0)| 00:00:01 |
|*  5 |  TABLE ACCESS FULL            | T2   |  3474 | 45162 |    74   (0)| 00:00:01 |
--------------------------------------------------------------------------------------