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

oracle中約束（constraints）是如何影響查詢計劃的，oracleconstraints
原文：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.