约束条件对于查询优化的作用

约束条件对于查询优化至关重要。许多人仅仅认识到约束是为了保证数据的完整性，当然这也是对的。但约束同事也会被优化器利用以便决定最优执行计划。优化器使用以下数据作为输入变量： 1. 查询语句 2. 所有可用的数据库对象统计值 3. 系统统计值，可能存在的如CPU速度，单块物理读的速度，以及一系列硬件指标 4. 数据库初始化参数 (parameters) 优化器使用所有这些信息以便决定最好的查询方式。我常常遇到人们在数据仓库或报表系统中避免使用约束。 "或许他们不需要约束以保持数据完整性，但他们确实需要约束以获取最优执行计划。数据仓库中糟糕的执行计划可能执行数个小时乃至于数天。由于性能考量，数据仓库同样需要约束！让我来看一些例子（使用11gr1,11.1.0.7）。第一个例子是分区排除，该特性自版本7.3时引入。在代码演示1中，我们建立2个表包括互斥的数据以及一个合并(UNION ALL)它们的视图。代码演示1: 建立表以及互斥数据以及试图 SQL> create table t1 2 as 3 select * from all_objects 4 where object_type in ('TABLE','VIEW'); SQL> alter table t1 modify object_type not null; 表已更改。 SQL> alter table t1 add constraint t1_check_otype 2 check (object_type in ('TABLE','VIEW')); 表已更改。 SQL> create table t2 2 as 3 select * from all_objects 4 where object_type in ('SYNONYM','PROCEDURE'); SQL> alter table t2 modify object_type not null; 表已更改。表已创建。 SQL> alter table t2 add constraint t2_check_obype 2 check (object_type in ('SYNONYM','PROCEDURE')); 表已更改。 SQL> create or replace view v 2 as 3 select * from t1 4 union all 5 select * from t2; 视图已创建。代码演示2中我们使用object_type列查询视图并观察其执行计划。代码演示2： SQL> 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') 在代码演示2中的执行计划似乎没有避免读取T2表，请注意T2表上的object_type仅包括SYNONYMS和PROCEDURES类型。但我们可以看到该读表操作的上层检查，即第四步是过滤操作，该过滤的方式是 NULL is NOT NULL 这十分有趣，我们并没有写过这样的句子，但优化器为我们添加了他。由于 NULL IS NOT NULL是恒假的，所以实际上这系列操作(步骤4和5）其实永远不会发生。(你可以使用tkprof工具来确认这一点) 在下一个例子中，我们来体验一下为什么NOT NULL约束在涉及索引使用时特别重要。 SQL> create table t 2 as 3 select * from all_objects; Table created. SQL> create index t_idx on t(object_type); Index created. SQL> exec dbms_stats.gather_table_stats( user, 'T' ); PL/SQL procedure successfully completed. 现在，我们尝试计算表中的行数，优化器仅有一种方案，如代码演示3 代码演示3： SQL> set autotrace traceonly explain SQL> 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列是可为空的且索引不包含空键值(指索引相关的所有列皆为空的情况)，我们无法利用索引计算表上的行数，故不得不全表扫描。若我们告知数据库，OBJECT_TYPE列是非空的，执行计划将立即改变，如代码演示4 。代码演示4： SQL> alter table t modify object_type NOT NULL; Table altered. SQL> set autotrace traceonly explain SQL> 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 | ------------------------------------------------------------------------ 在object_type实际允许为空的情况呢？我们能做些什么呢？若我们可以在索引中加上非空的键值呢？显然计划将改变。在这里我把常数0加入索引。 SQL> drop index t_idx; Index dropped. SQL> create index t_idx on t (object_type, 0); Index created. 现在代码演示6中的执行计划趋向于使用索引了代码演示6： SQL> 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) 约束，主键，以及外键现在我们来测试主键，外键是如何影响优化器的。我们复制scott.emp和scott.dept表使用 DBMS_STATS.SET_TABLE_STATS改变他们的统计量使得它们看起来“十分庞大”，让优化器产生这种假象。代码演示7： SQL> create table emp 2 as 3 select * 4 from scott.emp; Table created. SQL> create table dept 2 as 3 select * 4 from scott.dept; Table created. SQL> create or replace view emp_dept 2 as 3 select emp.ename, dept.dname 4 from emp, dept 5 where emp.deptno = dept.deptno; View created. SQL> begin 2 dbms_stats.set_table_stats 3 ( user, 'EMP', numrows=>1000000, numblks=>100000 ); 4 dbms_stats.set_table_stats 5 ( user, 'DEPT', numrows=>100000, numblks=>10000 ); 6 end; 7 / PL/SQL procedure successfully completed. 我们同样使用一个view:emp_dept来返回以上2个表的连接查询结果，在该view仅返回emp表上数据时(ename列),我们发现执行计划需要读取emp与dept两个表，如代码演示8。代码演示8： 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") 我们知道实际无需访问dept表，由于deptno是dept表的主键，而emp表中的dept实际是外键。现在我们加上这层约束关系。 SQL> alter table dept add constraint dept_pk primary key(deptno); Table altered. SQL> alter table emp add constraint emp_fk_dept foreign key(deptno) 2 references dept(deptno); Table altered. 试看代码演示9中的执行计划代码演示9： SQL> 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) 如上dept表在以上查询中无需再在考虑之内了，故也谈不上哈希连接了，多出了一个断言:deptno是非空的。优化器意识到外键和逐渐的存在，以上查询实际等效于 SELECT ENAME FROM EMP WHERE DEPTNO IS NOT NULL。优化器舍弃了不必要的表，获得了响应时间上的进步。同时也证明了实际应用中不因该总是使用SELECT *以简化应用的实施。 NULL对于索引的影响:

Indexes and NULLs

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 10.2.0.4 - Release: 9.2 to 10.2 Information in this document applies to any platform.
Purpose
This article illustrates some common reasons why indexes are not selected when NULLs are present.
Scope and Application
This is a basic level overview with examples of index usage.
Indexes and NULLs

Indexes and NULLs
When dealing with indexes, a common mistake is to forget about NULLs. Indexes do not store NULL values and so indexes on NULLable columns can't be used to drive queries unless there is something that eliminates the NULL values from the query. To illustrate this are a number of examples based upon the following table/indexes:
drop table nulltest;
create table nulltest ( col1 number, col2 number,
col3 number not null, col4 number not null);
create index nullind1 on nulltest (col1);
create index notnullind3 on nulltest (col3);

begin
for i in 1..10000 loop
insert into nulltest values (i,i,i,i);
if i mod 1000 = 0 then
commit;
end if;
end loop;
end;
/

analyze table nulltest compute statistics;
Illustrative Queries:
select col1 from nulltest t;
select /*+ index(t nullind1) */ col1 from nulltest t;

select /*+ index(t) */ col1 from nulltest t;
select /*+ index(t notnullind3) */ col1 from nulltest t;
select /*+ index(t notnullind3) */ col3 from nulltest t;

select /*+ index(t nullind1) */ col1 from nulltest t
where col1 between 0 and 20000;

select col1 from nulltest t
where col1 is not null;
Queries and Explanations:
SQL> select col1 from nulltest t;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=10000 Bytes=30000)
   1    0   TABLE ACCESS (FULL) OF 'NULLTEST' (Cost=6 Card=10000 Bytes=30000)
col1 is NULLable so the index cannot be used with no predicate
SQL> select /*+ index(t nullind1) */ col1 from nulltest t;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=10000 Bytes=30000)
   1    0   TABLE ACCESS (FULL) OF 'NULLTEST' (Cost=6 Card=10000 Bytes=30000)
hinting the index on col1 (nullind1) makes no difference since col1 is NULLable
SQL> select /*+ index(t) */ col1 from nulltest t;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=10000 Bytes=30000)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'NULLTEST' (Cost=49 Card=10000 Bytes=30000)
   2    1     INDEX (FULL SCAN) OF 'NOTNULLIND3' (NON-UNIQUE) (Cost=20 Card=10000)
An open index hint on the table allows the selection of the index on the NOT NULL column (col3). Notice that the col3 predicate is not included anywhere in the query. In order for col1 to be retrieved, the table has to be accessed.
SQL> select /*+ index(t notnullind3) */ col1 from nulltest t;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=10000 Bytes=30000)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'NULLTEST' (Cost=49 Card=10000 Bytes=30000)
   2    1     INDEX (FULL SCAN) OF 'NOTNULLIND3' (NON-UNIQUE) (Cost=20 Card=10000)
hinting notnullind3 directly works as well
SQL> select /*+ index(t notnullind3) */ col3 from nulltest t;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=10000 Bytes=30000)
   1    0   INDEX (FULL SCAN) OF 'NOTNULLIND3' (NON-UNIQUE) (Cost=20 Card=10000 Bytes=30000)
Selecting the NOT NULL column (col3) works fine and uses the index with no table access.
SQL> select /*+ index(t nullind1) */ col1 from nulltest t
  where col1 between 0 and 20000;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=10000 Bytes=30000)
   1    0   INDEX (RANGE SCAN) OF 'NULLIND1' (NON-UNIQUE) (Cost=20 Card=10000 Bytes=30000)
The effect of the predicate against col1 is to eliminate nulls from the data returned from the column. This allows the index to be used.
SQL> select col1 from nulltest t
  2  where col1 is not null;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=10000 Bytes=30000)
   1    0   INDEX (FAST FULL SCAN) OF 'NULLIND1' (NON-UNIQUE) (Cost=5 Card=10000 Bytes=30000)
This example illustrates that forcing the column to return only NOT NULL values allows the index to be used. Note that in the previous example, the Index hint prevents an index fast full scan operation from being selected. An INDEX_FFS hint must be supplied to force an index fast full scan.

posted on 2009-08-17 11:44 Oracle和MySQL 阅读(252) 评论(0) 编辑收藏举报

刷新页面返回顶部

ORACLE数据库服务热线：13764045638 诗檀软件

约束条件对于查询优化的作用

Indexes and NULLs

Applies to:

Purpose

Scope and Application

Indexes and NULLs

Indexes and NULLs

Illustrative Queries:

Queries and Explanations:

导航

公告