用hint NO_UNNEST 可以禁止CBO 进行 Subquery Unnesting
hint UNNEST 可以提示CBO进行Subquery Unnesting
1.NO_UNNEST
SQL> set linesize 200
SQL> set pagesize 200
SQL> explain plan for select count(*) from test o where object_id>(select avg(object_id) from test i where i.object_type=o.object_type);
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 731730653
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 699 (2)| 00:00:09 |
| 1 | SORT AGGREGATE | | 1 | 38 | | |
|* 2 | HASH JOIN | | 53949 | 2002K| 699 (2)| 00:00:09 |
| 3 | VIEW | VW_SQ_1 | 45 | 1080 | 350 (2)| 00:00:05 |
| 4 | HASH GROUP BY | | 45 | 630 | 350 (2)| 00:00:05 |
| 5 | TABLE ACCESS FULL| TEST | 86974 | 1189K| 347 (1)| 00:00:05 |
| 6 | TABLE ACCESS FULL | TEST | 86974 | 1189K| 347 (1)| 00:00:05 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="O"."OBJECT_TYPE")
filter("OBJECT_ID">"AVG(OBJECT_ID)")
已选择19行。
真实执行计划:
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID anxr90rp27yn9, child number 0
-------------------------------------
select count(*) from test o where object_id>(select avg(object_id) from
test i where i.object_type=o.object_type)
Plan hash value: 731730653
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.13 | 39796 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.13 | 39796 | | | |
|* 2 | HASH JOIN | | 1 | 53949 | 692K|00:00:01.09 | 39796 | 1229K| 1229K| 1557K (0)|
| 3 | VIEW | VW_SQ_1 | 1 | 45 | 45 |00:00:00.49 | 19898 | | | |
| 4 | HASH GROUP BY | | 1 | 45 | 45 |00:00:00.49 | 19898 | 123M| 10M| 2522K (0)|
| 5 | TABLE ACCESS FULL| TEST | 1 | 86974 | 1391K|00:00:00.16 | 19898 | | | |
| 6 | TABLE ACCESS FULL | TEST | 1 | 86974 | 1391K|00:00:00.16 | 19898 | | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="O"."OBJECT_TYPE")
filter("OBJECT_ID">"AVG(OBJECT_ID)")
已选择25行。
已用时间: 00: 00: 00.03
执行耗时:
SQL> select count(*) from test o where object_id>(select avg(object_id) from test i where i.object_type=o.object_type);
COUNT(*)
----------
692912
已用时间: 00: 00: 00.54
禁止cbo进行查询转换:
select count(*) from test o where object_id>(select /*+ NO_UNNEST */ avg(object_id) from test i where i.object_type=o.object_type);
SQL> explain plan for
select count(*) from test o where object_id>(select /*+ NO_UNNEST */ avg(object_id) from test i where i.object_type=o.object_type);
2
已解释。
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2139949240
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 15960 (1)| 00:03:12 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | TEST | 86974 | 1189K| 347 (1)| 00:00:05 |
| 4 | SORT AGGREGATE | | 1 | 14 | | |
|* 5 | TABLE ACCESS FULL| TEST | 1933 | 27062 | 347 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"> (SELECT /*+ NO_UNNEST */ AVG("OBJECT_ID")
FROM "TEST" "I" WHERE "I"."OBJECT_TYPE"=:B1))
5 - filter("I"."OBJECT_TYPE"=:B1)
已选择19行。
SQL>
select count(*) from test o where object_id>(select /*+ NO_UNNEST */ avg(object_id) from test i where i.object_type=o.object_type);
SQL>
COUNT(*)
----------
692912
已用时间: 00: 00: 18.45
SQL> SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5hzdf4w2c60sf, child number 0
-------------------------------------
select count(*) from test o where object_id>(select /*+ NO_UNNEST */
avg(object_id) from test i where i.object_type=o.object_type)
Plan hash value: 2139949240
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:18.45 | 4516K|
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:18.45 | 4516K|
|* 2 | FILTER | | 1 | | 692K|00:00:18.41 | 4516K|
| 3 | TABLE ACCESS FULL | TEST | 1 | 86974 | 1391K|00:00:00.16 | 19898 |
| 4 | SORT AGGREGATE | | 226 | 1 | 226 |00:00:17.87 | 4496K|
|* 5 | TABLE ACCESS FULL| TEST | 226 | 1933 | 1458K|00:00:17.72 | 4496K|
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">)
5 - filter("I"."OBJECT_TYPE"=:B1)
已选择24行。
已用时间: 00: 00: 00.03
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
