oracle中約束（constraints）是如何影響查詢計劃的

原文：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個差的履行計劃可能耗時幾小時乃至幾天才能履行完。下面我們通過實例來講明束縛對履行計劃的重要性：


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: 優化掉1個表
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束縛是如何作用的再來看1個例子：
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、來看1個NOT NULL和主外鍵束縛搭配物化視圖查詢是如何影響履行計劃的
我常常把物化視圖作為”數據倉庫索引“使用，其最重要的用處是作為“preanswer”，將針對特定表的復雜和長時間運行的結果保存在1個永久表中。
說白了就是加速查詢速度。
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: 1個查詢使用物化視圖
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: 1個查詢沒有使用物化視圖
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⑴258720/

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

Dylan    Presents.