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