關於查詢塊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" ")
      USE_HASH(@"SEL$5DA710D3" ")
      LEADING(@"SEL$5DA710D3" " ")
      INDEX_FFS(@"SEL$5DA710D3" " ("WXH_TBD1"."OBJECT_ID"))
      FULL(@"SEL$5DA710D3" ")
      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"."OBJECT_ID"))
      FULL(@"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生成一個查詢塊