Transactions - Oracle Database Transactions and Locking Revealed - page 90

And that's the way it works. Note that to defer a constraint, you must create it that way—you have to drop and
re-create the constraint to change it from nondeferrable to deferrable. That might lead you to believe that you should
create all of your constraints as “deferrable initially immediate,” just in case you wanted to defer them at some point. In
general, that is not true. You want to allow constraints to be deferred only if you have a real need to do so. By creating
deferred constraints, you introduce differences in the physical implementation (in the structure of your data) that might
not be obvious. For example, if you create a deferrable UNIQUE or PRIMARY KEY constraint, the index that Oracle creates to
support the enforcement of that constraint will be a non-unique index. Normally, you expect a unique index to enforce
a unique constraint, but since you have specified that the constraint could temporarily be ignored, it can't use that
unique index. Other subtle changes will be observed, for example, with NOT NULL constraints. If you allow your NOT NULL
constraints to be deferrable, the optimizer will start treating the column as if it supports NULLs —because it in fact does
support NULLs during your transaction. For example, suppose you have a table with the following columns and data:
EODA@ORA12CR1> create table t
2 ( x int constraint x_not_null not null deferrable,
3 y int constraint y_not_null not null,
4 z varchar2(30)
5 );
Table created.
EODA@ORA12CR1> insert into t(x,y,z)
2 select rownum, rownum, rpad('x',30,'x')
3 from all_users;
45 rows created.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
In this example, column X is created such that when you COMMIT , X will not be null. However, during your
transaction X is allowed to be null since the constraint is deferrable. Column Y , on the other hand, is always NOT NULL .
Let's say you were to index column Y :
EODA@ORA12CR1> create index t_idx on t(y);
Index created.
And you then ran a query that could make use of this index on Y —but only if Y is NOT NULL , as in following query:
EODA@ORA12CR1> explain plan for select count(*) from t;
Explained.
EODA@ORA12CR1> select * from table(dbms_xplan.display(null,null,'BASIC'));
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX FULL SCAN| T_IDX |
----------------------------------