oracle延时约束的问题

约束可以是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;