Oracle programming Art Study Notes (17)-DEFERRABLE Constraints

 

By default, integrity constraints are checked after the entire statement is processed.

However, you can create a latency constraint to delay this constraint to COMMIT or another time before checking. Can be specified

· Deferrable initially immediate: Creates a delayed constraint, but the initial state is at the statement level. Initially immediate can also be left empty.

· Deferrable initially deferred: Creates a delayed constraint and the initial state is in the latency check.

 

For example:

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

);

Then execute the following statement and observe the result:

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

 

However, you must note that you must create a latency constraint only when you really need it. Latency constraints introduce imperceptible differences in physical implementation.

For example, for the table T created above, create indexes on column X, Y, and Z respectively, and then execute the select count (*) operation to view their respective execution plans.

 

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)

 

As you can see, the optimizer does not use indexes created on column Y and column Z to query the number of rows. In fact, it cannot be used because B * Tree indexes do not create entries for completely NULL index keys. Although Y and Z are not subject to NULL constraints, they are latency constraints, that is, temporarily NULL is allowed, so the optimizer cannot use them to query the number of rows.

 

In addition, if you create a unique or primary key constraint, oracle creates a unique index for this constraint. However, if one delayed unique or primary key constraint is created, oracle can only create one non-unique index because the constraint can be temporarily ignored.

For example:

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

 

From NowOrNever