Deferrable constraints can prevent optimizer transformations. Here's a simple example validating percentages must be between zero and one hundred:
create table exam_results (
student_id integer, exam_id integer,
percentage_correct number,
primary key ( student_id, exam_id )
);
alter table exam_results
add constraint exre_pct_correct_c
check ( percentage_correct between 0 and 100 );
With a not deferrable constraint the optimizer knows if you search for values outside this range the query must return no rows. So if you do it can apply a transformation to bypass the table:
set serveroutput off
select * from exam_results
where percentage_correct < 0;
select *
from dbms_xplan.display_cursor ( format => 'BASIC +PREDICATE' );
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
|* 2 | TABLE ACCESS STORAGE FULL| EXAM_RESULTS |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - storage("PERCENTAGE_CORRECT"<0)
filter("PERCENTAGE_CORRECT"<0)
Notice the filter operation NULL IS NOT NULL is a parent of the full scan operation. Because it's false, there's no need to run the scan. The optimizer has stopped the query accessing the table at all!
If you re-create the constraint as deferrable, then you may temporarily have invalid data. This applies even if you declare it initially immediate because you can change the state to deferred within sessions/transactions.
So the optimizer can no longer rely on the constraint:
alter table exam_results
drop constraint exre_pct_correct_c;
alter table exam_results
add constraint exre_pct_correct_c
check ( percentage_correct between 0 and 100 )
deferrable initially immediate;
select * from exam_results
where percentage_correct < 0;
select *
from dbms_xplan.display_cursor ( format => 'BASIC +PREDICATE' );
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS STORAGE FULL| EXAM_RESULTS |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("PERCENTAGE_CORRECT"<0)
filter("PERCENTAGE_CORRECT"<0)
The plan no longer has filter operation. The database reads every row in the table, checking to see if the percentage is below zero.
TL;DR Without the guarantees validated, not deferrable constraints bring, you limit the optimizer's ability to generate better plans.
