约束可以是deferrable或not deferrable(默认)。
可延时约束在创建后可以修改成约束延时检查
deferrable表示该约束是可延迟验证的. 它有两个选项:
Initially immediate(默认): 立即验证, 执行完一个sql后就进行验证;
Initially deferred: 延迟验证
下面的例子是摘自oracle编程艺术
create table t(x int constraint x_not_null not null deferrable,y int constraint y_not_null not null, z varchar2(20));
SQL> insert into t select rownum,rownum,rpad('x',20,'x') from all_users;
32 rows created.
SQL> commit;
Commit complete.
SQL> explain plan for select count(*) from t;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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 | 32 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- dynamic sampling used for this statement (level=2)
13 rows selected.
SQL> create index idx_x on t(x);
Index created.
SQL> explain plan for select count(*) from t;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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 | 32 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- dynamic sampling used for this statement (level=2)
13 rows selected.
SQL> create index idx_y on t(y);
Index created.
SQL> explain plan for select count(*) from t;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4266017364
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IDX_Y | 32 | 1 (0)| 00:00:01 |
------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- dynamic sampling used for this statement (level=2)
13 rows selected.
可以看到x上的索引没有被使用到,因为指定了这个not null的约束是可以延时的,也就是说这个列中能保存空值,所以无法使用x上的索引
设置约束为延时约束
SQL> set constraint x_not_null deferred;