首页 > 代码库 > oracle中约束（constraints）是如何影响查询计划的

oracle中约束（constraints）是如何影响查询计划的

2024-10-20 17:51:38   206人阅读

原文：http://www.oracle.com/technetwork/issue-archive/2009/09-may/o39asktom-096149.html


oracle中约束（constraints）是如何影响查询计划的


通常人们认为约束只是和数据完整性有关，没问题。但是约束也被优化器使用来优化执行计划。
优化器会拿以下资源最为输入inputs：


1）待优化的查询
2）所有数据库对象统计
3）系统统计，如果可以获取的话（CPU速度、单块I/O速度等等作为物理硬件的衡量尺度）
4）初始化参数
5）约束


我经常听到人们在数据仓库/报表系统中忽略约束的使用，理由是：数据本身OK，并且我们做了数据清洗（如果让约束enable，他们反而不高兴），但事实证明为了获得更好的执行计划他们的确需要数据完整性约束。数据仓库中一个差的执行计划可能耗时几小时甚至几天才能执行完。下面我们通过实例来说明约束对于执行计划的重要性：


1. 来看NOT NULL约束如何影响执行计划：
code1: 创建数据隔离的表和视图
drop table t1;
drop table t2;
drop view v;


create table t1
as
select * from all_objects
where object_type in (‘TABLE‘,‘VIEW‘);




alter table t1 modify object_type not null;


alter table t1 add constraint t1_check_otype check (object_type in(‘TABLE‘,‘VIEW‘));




create table t2
 as
 select * from all_objects
 where object_type in ( ‘SYNONYM‘, ‘PROCEDURE‘ );




alter table t2 modify object_type not null;


alter table t2 add constraint t2_check_otype 
  check (object_type in (‘SYNONYM‘, ‘PROCEDURE‘));




create or replace view v
as
select * from t1
union all
select * from t2;


code2: 优化掉一个表
set autotrace traceonly explain
select * from v where object_type = ‘TABLE‘;
Execution Plan
----------------------------------------------------------------------------
Plan hash value: 3982894595


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    40 |  6320 |   151   (1)| 00:00:02 |
|   1 |  VIEW                | V    |    40 |  6320 |         (1)| 00:00:02 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL | T1   |  3083 |   475K|    31   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| T2   |     5 |   790 |   12    (1)| 00:00:02 |
-----------------------------------------------------------------------------


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


   3 - filter("OBJECT_TYPE"=‘TABLE‘)
   4 - filter(NULL IS NOT NULL)
   5 - filter("OBJECT_TYPE"=‘TABLE‘)


奇怪的是：我们查的数据只在T1中存在根本不关T2的事！而且看filter4：NULL IS NOT NULL这个条件我们没有指定，是执行计划加的！
这个条件始终未FALSE。


继续，为了说明NOT NULL约束是如何作用的再来看一个例子：
code3:
drop table t;
create table t
  as
  select * from all_objects;


create index t_idx on t(object_type);


exec dbms_stats.gather_table_stats( user, ‘T‘ );


select count(*) from t;
Execution Plan
----------------------------------------
Plan hash value: 2966233522


-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   283   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 68437 |   283   (1)| 00:00:04 |
-------------------------------------------------------------------


这个计划没有使用我们创建的索引。原因是object_type列是nullable，索引并不包含所有NULL值，所以无法根据索引键值进行
COUNT操作。如果我们告诉数据库：OBJECT_TYPE IS NOT NULL，执行计划将立马转变！
code4:
alter table t modify object_type NOT NULL;
select count(*) from t;
Execution Plan
------------------------------------------
Plan hash value: 1058879072


------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows   | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1  |    54   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1  |            |          |
|   2 |   INDEX FAST FULL SCAN| T_IDX | 68437  |    54   (2)| 00:00:01 |
------------------------------------------------------------------------


some kind of 神奇吧！
问题是加入该object_type列可以为NULL，又该如何解决？答案是我们可以创建多列索引（组合索引）当然object_type比在其中。
例如：
code5:
drop index t_idx;
create index t_idx on t (object_type, 0);
code6:
select * from t where object_type is null;
Execution Plan
-----------------------------
Plan hash value: 470836197


--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |   101 |  1      (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T     |     1 |   101 |  1      (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN           | T_IDX |     1 |       |  1      (0)| 00:00:01 |
--------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_TYPE" IS NULL)
   


2、来看主外键约束如何影响执行计划：
code7:
drop table emp;
drop table dept;
drop view emp_dept;


create table emp
  as
  select *
  from scott.emp;


create table dept
  as
  select *
  from scott.dept;


create or replace view emp_dept
  as
  select emp.ename, dept.dname
    from emp, dept
   where emp.deptno = dept.deptno; 


--我们假装EMP和DEPT两个表示大表！
begin
     dbms_stats.set_table_stats
         ( user, ‘EMP‘, numrows=>1000000, numblks=>100000 );
     dbms_stats.set_table_stats
         ( user, ‘DEPT‘, numrows=>100000, numblks=>10000 );
  end; 
  /
  
code8:
SQL> select ename from emp_dept;


Execution Plan
-----------------------------
Plan hash value: 615168685


----------------------------------------------------------------------------------------
| Id   | Operation          | Name  | Rows  |  Bytes |TempSpc | Cost (%CPU) | Time     |
----------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT   |       |  1000K|     31M|        | 31515    (1)| 00:06:19 |
|*   1 |  HASH JOIN         |       |  1000K|     31M|   2448K| 31515    (1)| 00:06:19 |
|    2 |   TABLE ACCESS FULL| DEPT  |   100K|   1269K|        |  2716    (1)| 00:00:33 |
|    3 |   TABLE ACCESS FULL| EMP   |  1000K|     19M|        | 27151    (1)| 00:05:26 |
----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")


此处我们只查询EMP表的ename列，DEPT表现得没啥必要。但是DEPTNO是DEPT表的主键,EMP表的外键！这样就导致EMP表中DEPTNO列是非空的。但是我们没有指明这层关系，
ORACLE自然不知道，所以我们这么做：
alter table dept add constraint dept_pk primary key(deptno);


alter table emp add constraint emp_fk_dept foreign key(deptno) references dept(deptno);


select ename from emp_dept;


Execution Plan
------------------------------
Plan hash value: 3956160932


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50000 |   976K| 27152   (1)| 00:05:26 |
|*  1 |  TABLE ACCESS FULL| EMP  | 50000 |   976K| 27152   (1)| 00:05:26 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMP"."DEPTNO" IS NOT NULL)


起作用了！加了个filter。
  
3、来看一个NOT NULL和主外键约束搭配物化视图查询是如何影响执行计划的
我经常把物化视图作为”数据仓库索引“使用，其最重要的用途是作为“preanswer”，将针对特定表的复杂和长时间运行的结果保存在一个永久表中。
说白了就是加速查询速度。
alter table emp drop constraint emp_fk_dept;


alter table dept drop constraint dept_pk;


code10:
create materialized view mv enable query rewrite
  as
  select dept.deptno, dept.dname, count (*) from emp, dept
   where emp.deptno = dept.deptno
   group by dept.deptno, dept.dname;


begin
    dbms_stats.set_table_stats
    ( user, ‘MV‘, numrows=>100000, numblks=>10000 );
end; 
/


code11: 一个查询使用物化视图
select dept.dname, count (*) from emp, dept
   where emp.deptno = dept.deptno and dept.dname = ‘SALES‘
   group by dept.dname; 


Execution Plan
------------------------------
Plan hash value: 1703036361


--------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  |  Bytes| Cost (%CPU)  | Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |  1000 | 22000 |  2716     (1)| 00:00:33 |
|   1 |  SORT GROUP BY NOSORT             |      |  1000 | 22000 |  2716     (1)| 00:00:33 |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL    | MV   |  1000 | 22000 |  2716     (1)| 00:00:33 |
--------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("MV"."DNAME"=‘SALES‘)


code12: 一个查询没有使用物化视图
SQL> select count(*) from emp;


COUNT(*)
--------
      14


SQL> select * from table(dbms_xplan.display_cursor);


PLAN_TABLE_OUTPUT
-----------------------
SQL_ID  g59vz2u4cu404, child number 1
-----------------------
select count(*) from emp


Plan hash value: 2083865914


-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       | 27142 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |  1000K| 27142   (1)| 00:05:26 |
-------------------------------------------------------------------
14 rows selected.




很明显，更有的执行计划应该是查询物化视图中实现计算好的数据。
为此，我们需要告诉数据库以下内容：
DEPTNO in DEPT is a primary key
DEPTNO in EMP is a foreign key
DEPTNO in EMP is a NOT NULL column


alter table dept add constraint dept_pk primary key(deptno);


alter table emp add constraint emp_fk_dept foreign key(deptno) references dept(deptno);


alter table emp modify deptno NOT NULL;


code13:
SQL> select count(*) from emp;


COUNT(*)
-------
     14


SQL> select * from table(dbms_xplan.display_cursor);


PLAN_TABLE_OUTPUT
-----------------------
SQL_ID  g59vz2u4cu404, child number 2
-----------------------
select count (*) from emp


Plan hash value: 1747602359


--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows |  Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      |        |  2716 (100)|          |
|   1 |  SORT AGGREGATE               |      |    1 |     13 |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| MV   |  100K|   1269K| 2716    (1)| 00:00:33 |
--------------------------------------------------------------------------------------




关于物化视图的查询重写特性参考：http://blog.itpub.net/28719055/viewspace-1258720/



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

Dylan    Presents.









oracle中约束（constraints）是如何影响查询计划的


联系
我们