explain plan for
select * from wxh_tbd where not exists (select /*+ unnest */1 from wxh_tbd1 where wxh_tbd1.object_id=wxh_tbd.object_id);
select * from wxh_tbd where not exists (select /*+ unnest */1 from wxh_tbd1 where wxh_tbd1.object_id=wxh_tbd.object_id);
Explained.
select * from table(dbms_xplan.display(null,null,'outline'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 325289001
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 325289001
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19942 | 1908K| 76 (3)| 00:00:01 |
|* 1 | HASH JOIN RIGHT ANTI | | 19942 | 1908K| 76 (3)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| T_22 | 20411 | 99K| 12 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | WXH_TBD | 20254 | 1839K| 63 (2)| 00:00:01 |
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19942 | 1908K| 76 (3)| 00:00:01 |
|* 1 | HASH JOIN RIGHT ANTI | | 19942 | 1908K| 76 (3)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| T_22 | 20411 | 99K| 12 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | WXH_TBD | 20254 | 1839K| 63 (2)| 00:00:01 |
---------------------------------------------------------------------------------
Outline Data
-------------
-------------
/*+
BEGIN_OUTLINE_DATA
SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "WXH_TBD1"@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "WXH_TBD1"@"SEL$2")
LEADING(@"SEL$5DA710D3" "WXH_TBD"@"SEL$1" "WXH_TBD1"@"SEL$2")
INDEX_FFS(@"SEL$5DA710D3" "WXH_TBD1"@"SEL$2" ("WXH_TBD1"."OBJECT_ID"))
FULL(@"SEL$5DA710D3" "WXH_TBD"@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
UNNEST(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
ALL_ROWS
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_complex_view_merging' 'false')
DB_VERSION('11.1.0.7')
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
BEGIN_OUTLINE_DATA
SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "WXH_TBD1"@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "WXH_TBD1"@"SEL$2")
LEADING(@"SEL$5DA710D3" "WXH_TBD"@"SEL$1" "WXH_TBD1"@"SEL$2")
INDEX_FFS(@"SEL$5DA710D3" "WXH_TBD1"@"SEL$2" ("WXH_TBD1"."OBJECT_ID"))
FULL(@"SEL$5DA710D3" "WXH_TBD"@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
UNNEST(@"SEL$2")
OUTLINE_LEAF(@"SEL$5DA710D3")
ALL_ROWS
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_complex_view_merging' 'false')
DB_VERSION('11.1.0.7')
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
由于ORACLE自动对查询做了解嵌套,展开了子查询,因此生成了一个新的查询块SEL$5DA710D3。
看看强制不让展开子查询,查询块的命名是怎么样的。
explain plan for
select * from wxh_tbd where not exists (select /*+ no_unnest */1 from wxh_tbd1 where wxh_tbd1.object_id=wxh_tbd.object_id);
select * from wxh_tbd where not exists (select /*+ no_unnest */1 from wxh_tbd1 where wxh_tbd1.object_id=wxh_tbd.object_id);
Explained.
select * from table(dbms_xplan.display(null,null,'outline'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3960225502
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3960225502
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20253 | 1839K| 10194 (1)| 00:02:03 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| WXH_TBD | 20254 | 1839K| 63 (2)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_22 | 1 | 5 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20253 | 1839K| 10194 (1)| 00:02:03 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| WXH_TBD | 20254 | 1839K| 63 (2)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_22 | 1 | 5 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Outline Data
-------------
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$2" "WXH_TBD1"@"SEL$2" ("WXH_TBD1"."OBJECT_ID"))
FULL(@"SEL$1" "WXH_TBD"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
ALL_ROWS
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_complex_view_merging' 'false')
DB_VERSION('11.1.0.7')
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
BEGIN_OUTLINE_DATA
INDEX(@"SEL$2" "WXH_TBD1"@"SEL$2" ("WXH_TBD1"."OBJECT_ID"))
FULL(@"SEL$1" "WXH_TBD"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
ALL_ROWS
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_complex_view_merging' 'false')
DB_VERSION('11.1.0.7')
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
从上面看到查询块的命名就是SEL$1,SEL$2的命名。
看来即使对于同样的一个查询语句,查询块的命名也可能出现不同。ORACLE根据最终转换生成的语句来为最终的SQL生成一个查询块
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-715449/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-715449/