首页 > 数据库 > Oracle >

《Oracle编程艺术》学习笔记(17)-DEFERRABLE约束

2011-10-30

默认情况下,完整性约束在整个语句得到处理后进行检查。但是可以创建延迟约束,把这个约束延迟到COMMIT或另外某个时间才检查。可以指定为·DEFERRABLE INITIALLY IMMEDIATE:创建可延迟的约束,但是初始状态是...

默认情况下,完整性约束在整个语句得到处理后进行检查。

但是可以创建延迟约束,把这个约束延迟到COMMIT或另外某个时间才检查。可以指定为

·DEFERRABLE INITIALLY IMMEDIATE:创建可延迟的约束,但是初始状态是在语句级检查。INITIALLY IMMEDIATE也可以不写。

·DEFERRABLE INITIALLY DEFERRED: 创建可延迟的约束,而且初始状态是在延迟检查。

例如:

create table t

( x int constraint x_not_null not null,

y int constraint y_not_null not null deferrable initially immediate,

z int constraint z_not_null not null deferrable initially deferred

);

然后执行下面语句,观察结果:

view plain

tony@ORA11GR2> insert into t values(1, 2, null);

1 row created.

tony@ORA11GR2> set constraint z_not_null immediate;

set constraint z_not_null immediate

*

ERROR at line 1:

ORA-02290: check constraint (TONY.Z_NOT_NULL) violated

tony@ORA11GR2> insert into t values(1, null, null);

insert into t values(1, null, null)

*

ERROR at line 1:

ORA-02290: check constraint (TONY.Y_NOT_NULL) violated

tony@ORA11GR2> commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-02290: check constraint (TONY.Z_NOT_NULL) violated

但是需要注意,应当在只有真正需要的时候才创建延迟约束。延迟约束会在物理实现上引入不易觉察的差别。

例如,还是对上面创建的表格T,在列X,Y,Z上分别创建索引,然后执行select count(*)操作,察看各自的执行计划。

view plain

tony@ORA11GR2> insert into t values(1, 2, 3);

1 row created.

tony@ORA11GR2> set autotrace on explain

tony@ORA11GR2> create index t_idx on t(x);

Index created.

tony@ORA11GR2> select count(*) from t;

COUNT(*)

----------

1

Execution Plan

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

Plan hash value: 995313729

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

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FULL SCAN| T_IDX | 1 | 1 (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

tony@ORA11GR2> drop index t_idx;

Index dropped.

tony@ORA11GR2> create index t_idx on t(y);

Index created.

tony@ORA11GR2> select count(*) from t;

COUNT(*)

----------

1

Execution Plan

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

Plan hash value: 2966233522

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

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| T | 1 | 3 (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

tony@ORA11GR2> drop index t_idx;

Index dropped.

tony@ORA11GR2> create index t_idx on t(z);

Index created.

tony@ORA11GR2> select count(*) from t;

COUNT(*)

----------

1

Execution Plan

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

Plan hash value: 2966233522

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

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| T | 1 | 3 (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

可以看到,优化器不会使用在列Y,Z上建立的索引来查询行数。实际上也不能使用,这是因为B*Tree索引不会为完全为NULL的索引键建立条目,Y和Z虽然加了不为NULL的约束,但是是延迟约束,也就是允许临时为NULL,所以优化器无法使用它们来查询行数。

还有,如果创建1个unique或者primary key约束,oracle会为这个约束创建1个唯一索引。但是如果创建1个延迟unique或者primary key约束,由于可以临时忽略约束,oracle就只能创建1个非唯一索引。

例如:

view plain

tony@ORA11GR2> drop table t;

Table dropped.

tony@ORA11GR2> create table t(x int primary key);

Table created.

tony@ORA11GR2> select index_name, uniqueness from user_indexes where table_name='T';

INDEX_NAME UNIQUENESS

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

SYS_C0011412 UNIQUE

tony@ORA11GR2> drop table t;

Table dropped.

tony@ORA11GR2> create table t(x int primary key deferrable);

Table created.

tony@ORA11GR2> select index_name, uniqueness from user_indexes where table_name='T';

INDEX_NAME UNIQUENESS

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

SYS_C0011413 NONUNIQUE

摘自 NowOrNever

相关文章
最新文章
热点推荐