关于查询块query block

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

Explained.

select * from table(dbms_xplan.display(null,null,'outline'));

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

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

Explained.

select * from table(dbms_xplan.display(null,null,'outline'));

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

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/