oracle查詢語句執行計劃中的表消除

eric0435發表於2013-10-26
Oracle

oracle查詢語句執行計劃中的表消除
在10gR2中,引入的新的轉換表消除(也可以叫連線消除),它將從查詢中移除冗餘的表.如果一個表的列僅僅只在連線謂詞中出現那麼這個表是冗餘的且它被用來保證這些連線既不執行過濾也不擴充套件結果集.oracle在以下幾種情況下將會消除冗餘表.
主鍵-外來鍵表消除
從10gr2開始,最佳化器能將由於主鍵-外來鍵約束造成的冗餘表消除例如:

jy@JINGYONG> create table jobs
  2  ( job_id NUMBER PRIMARY KEY,
  3  job_title VARCHAR2(35) NOT NULL,
  4  min_salary NUMBER,
  5  max_salary NUMBER );

表已建立。

jy@JINGYONG> create table departments
  2  ( department_id NUMBER PRIMARY KEY,
  3  department_name VARCHAR2(50) );

表已建立。


jy@JINGYONG> create table employees
  2  ( employee_id NUMBER PRIMARY KEY,
  3    employee_name VARCHAR2(50),
  4    department_id NUMBER REFERENCES departments(department_id),
  5    job_id NUMBER REFERENCES jobs(job_id) );

表已建立。

然後執行下面的查詢:

jy@JINGYONG> select e.employee_name
  2  from employees e, departments d
  3  where e.department_id = d.department_id;

未選定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  91p4shqr32mcy, child number 0
-------------------------------------
select e.employee_name from employees e, departments d where
e.department_id = d.department_id

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    40 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)

在上面的查詢中,連線department表是冗餘的.department表中只有一列出現在連線謂詞中且主鍵-外來鍵約束保證了對於employees表中的每一行在department表中最多隻有一行與之匹配.因此,上面的查詢與下面的查詢是等價的:

jy@JINGYONG> select e.employee_name
  2  from employees e
  3  where e.department_id is not null;

未選定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))
;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4dk02pkcxh604, child number 0
-------------------------------------
select e.employee_name from employees e where e.department_id is not
null

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    40 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)

注意如果表employees中的department列上有not null約束上面的is not null謂詞不是必需的.從oracle11gr開始,最佳化器將會消除哪些半連線或反連線的表,例如下面的查詢:

jy@JINGYONG> select e.employee_id, e.employee_name
  2  from employees e
  3  where not exists (select 1
  4                    from jobs j
  5                    where j.job_id = e.job_id);

未選定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))
;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  2swr3q3drtycz, child number 0
-------------------------------------
select e.employee_id, e.employee_name from employees e where not exists
(select :"SYS_B_0"                   from jobs j
where j.job_id = e.job_id)

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    53 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."JOB_ID" IS NULL)

因為employees.job_id是引用jobs.job_id的一個外來鍵,對於employees.job_id中的任何不為null的值在jobs表中必需有一個值與之匹配.所以只有employees.job_id為null值的記錄才會出現在結果集中.因此上面的查詢與下面的查詢是等價的:

jy@JINGYONG> select e.employee_id, e.employee_name
  2  from employees e
  3  where job_id is null;

未選定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))
;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6uh0534dch5m3, child number 0
-------------------------------------
select e.employee_id, e.employee_name from employees e where job_id is
null

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    53 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("JOB_ID" IS NULL)

如果employees.job_id有一個not null約束的話:

jy@JINGYONG> alter table employees modify job_id not null;

表已更改。

那麼在這種情況下對於上面的查詢語句在employees表中沒有滿足條件的記錄,查詢最佳化器可能會選下面的執行執行:

jy@JINGYONG> select e.employee_id, e.employee_name
  2  from employees e
  3  where job_id is null;

未選定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))
;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6uh0534dch5m3, child number 0
-------------------------------------
select e.employee_id, e.employee_name from employees e where job_id is
null

Plan hash value: 72609621

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |       |       |     1 (100)|          |

|*  1 |  FILTER            |           |       |       |            |          |

|   2 |   TABLE ACCESS FULL| EMPLOYEES |     1 |    53 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)

上面謂詞中的"NULL IS NOT NULL"過濾是一個虛假的常量謂詞它將阻止即將發生的表掃描.

在oracle11gR1中對於ANSI相容的連線最佳化器也能正確的執行表消除,例如:

jy@JINGYONG> select employee_name
  2  from employees e inner join jobs j
  3  on e.job_id = j.job_id;

未選定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))
;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6m6g9pfuvpb69, child number 0
-------------------------------------
select employee_name from employees e inner join jobs j on e.job_id =
j.job_id

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    27 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

從上面的執行計劃可知最佳化器正確的消除了冗餘表jobs

外連線表消除
在oracle11gr1中對於外連線引入了一種新的表消除,它不要求主鍵-外來鍵約束.例如:先建立一個新的表projects並向employees表中增加project_id列

jy@JINGYONG> create table projects
  2  ( project_id NUMBER UNIQUE,
  3  deadline DATE,
  4  priority NUMBER );

表已建立。

jy@JINGYONG> alter table employees add project_id number;

表已更改。

現在來執行一個外連線查詢:

jy@JINGYONG> select e.employee_name, e.project_id
  2  from employees e, projects p
  3  where e.project_id = p.project_id (+);

未選定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))
;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bdzav4h1rzn6n, child number 0
-------------------------------------
select e.employee_name, e.project_id from employees e, projects p where
e.project_id = p.project_id (+)

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    40 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

外連線保證employees表中的每一行將會至少在結果集中出現一次.這唯一約束projects.project_id用來保證在employees中的每一行在projects表中最多有一行與之匹配.這兩個屬性一起保證了employees表中的每一行正好在結果集中出現一次.因為表projects中沒有其它列被引用,projects表能被消除所以最佳化器選擇了上面的查詢.

在上面執行的查詢都是非常簡單的查詢,在實際情況不可能都是那樣簡單的查詢.但是在實際情況下表消除也是有好處的包括機器生成的查詢和檢視中的表消除.例如,一組表可能透過檢視來提供訪問,其中可能包含連線.透過檢視來訪問所有的列這個連線可能是必需的.但是有些使用者可能只訪問這個檢視中的一部分列,在這種情況下有些連線表可能會被消除:

jy@JINGYONG> create view employee_directory_v as
  2  select e.employee_name, d.department_name, j.job_title
  3  from employees e, departments d, jobs j
  4  where e.department_id = d.department_id
  5  and e.job_id = j.job_id;

檢視已建立。

如果要從上面的檢視中透過職稱來檢視僱員的名字可以使用類似下面的查詢:

jy@JINGYONG> select employee_name
  2  from employee_directory_v
  3  where department_name = 'ACCOUNTING';

未選定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))

  2  ;

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  4dfdc0m1d05c0, child number 0
-------------------------------------
select employee_name from employee_directory_v where department_name =
:"SYS_B_0"

Plan hash value: 2170245257

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |       |       |     3 (100)|          |
|   1 |  NESTED LOOPS                |              |       |       | |          |
|   2 |   NESTED LOOPS               |              |     1 |    80 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | EMPLOYEES    |     1 |    40 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0011146 |     1 |       |     0   (0)|          |
|*  5 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS  |     1 |    40 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   5 - filter("D"."DEPARTMENT_NAME"=:SYS_B_0)

由於job_title列沒有被select子句引用,jobs表從這個查詢中被消除了所以最佳化器選擇了上面的執行計劃.

目前對於表消除有以下限制:
1.多列的主鍵-外來鍵約束不支援
2.在查詢中引用其它的連線鍵將阻止表消除.對於一個內聯連線,連線鍵在連線的每一邊都是等價的,但是如果
查詢透過連線鍵在select子句中引用了表中其它的列這將不會執行表消除.一個解決辦表是重寫查詢.

參考:
https://blogs.oracle.com/optimizer/entry/why_are_some_of_the_tables_in_my_query_missing_from_the_plan

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-775092/，如需轉載，請註明出處，否則將追究法律責任。

相關文章