oracle约束是什么,oracle中约束（constraints）是怎么影响查询计划的

当前位置:我的异常网» 人工智能 » oracle中约束(constraints)是怎么影响查询计划的

oracle中约束(constraints)是怎么影响查询计划的

www.myexceptions.net  网友分享于：2015-01-03  浏览：0次

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. 来看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.

文章评论