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 |
--------------------------------------------------------------------------------------
