RELY DISABLE

Learning, relearning or unforgetting…

What value is there in a DISABLEd constraint?

This was a question on the OTN forums this week and a) my first reply was wrong and b) I couldn’t find a clear demonstration elsewhere.

The key is RELY.

The documentation is clear.

RELY Constraints

The ETL process commonly verifies that certain constraints are true. For example, it can validate all of the foreign keys in the data coming into the fact table. This means that you can trust it to provide clean data, instead of implementing constraints in the data warehouse. You create a RELY constraint as follows:

ALTER TABLE sales ADD CONSTRAINT sales_time_fk FOREIGN KEY (time_id) REFERENCES times (time_id) RELY DISABLE NOVALIDATE;

This statement assumes that the primary key is in the RELY state. RELY constraints, even though they are not used for data validation, can:

– Enable more sophisticated query rewrites for materialized views. See Chapter 18, “Basic Query Rewrite” for further details.
– Enable other data warehousing tools to retrieve information regarding constraints directly from the Oracle data dictionary.

Creating a RELY constraint is inexpensive and does not impose any overhead during DML or load. Because the constraint is not being validated, no data processing is necessary to create it.

We can prove the value of a RELY DISABLEd CONSTRAINT by playing withTom Kyte’s illustrations on the value of ENABLEd constraints.

EMP/DEPT Table:

drop table emp;
drop table dept;

create table dept 
(deptno number(2)     not null,
 dname  varchar2(15),
 loc    varchar2(15));

insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30, 'SALES', 'CHICAGO');
insert into dept values (40, 'OPERATIONS', 'BOSTON');

create table emp
(empno    number(4) not null
,ename    varchar2(10)
,job      varchar2(9)
,mgr      number(4)
,hiredate date
,sal      number(7, 2)
,comm     number(7, 2)
,deptno   number(2) not null);

insert into emp values (7369, 'SMITH', 'CLERK',    7902, to_date('17-DEC-1980', 'DD-MON-YYYY'), 800, null, 20);
insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
insert into emp values (7521, 'WARD',  'SALESMAN', 7698, to_date('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
insert into emp values (7566, 'JONES', 'MANAGER',  7839, to_date('2-APR-1981',  'DD-MON-YYYY'), 2975, null, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698,to_date('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839,to_date('1-MAY-1981', 'DD-MON-YYYY'), 2850, null, 30);
insert into emp values (7782, 'CLARK', 'MANAGER', 7839,to_date('9-JUN-1981', 'DD-MON-YYYY'), 2450, null, 10);
insert into emp values (7788, 'SCOTT', 'ANALYST', 7566,to_date('09-DEC-1982', 'DD-MON-YYYY'), 3000, null, 20);
insert into emp values (7839, 'KING', 'PRESIDENT', null,to_date('17-NOV-1981', 'DD-MON-YYYY'), 5000, null, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN', 7698,to_date('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
insert into emp values (7876, 'ADAMS', 'CLERK', 7788,to_date('12-JAN-1983', 'DD-MON-YYYY'), 1100, null, 20);
insert into emp values (7900, 'JAMES', 'CLERK', 7698,to_date('3-DEC-1981', 'DD-MON-YYYY'), 950, null, 30);
insert into emp values (7902, 'FORD', 'ANALYST', 7566,to_date('3-DEC-1981', 'DD-MON-YYYY'), 3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782,to_date('23-JAN-1982', 'DD-MON-YYYY'), 1300, null, 10);

begin
  dbms_stats.set_table_stats
  ( user, 'EMP', numrows=>1000000, numblks=>100000 );
  dbms_stats.set_table_stats
  ( user, 'DEPT', numrows=>100000, numblks=>10000 );
end; 
/

First, there’s nearly always an oddity to observe or tangent to follow:

alter table dept add constraint dept_pk primary key(deptno);
alter table emp add constraint emp_fk_dept foreign key(deptno) references dept(deptno) rely disable novalidate;

Results in:

SQL Error: ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY
25158. 00000 -  "Cannot specify RELY for foreign key if the associated primary key is NORELY"
*Cause:    RELY is specified for the foreign key contraint, when the
           associated primary key constraint is NORELY.
*Action:   Change the option of the primary key also to RELY.

But this is ok?

alter table emp add constraint emp_fk_dept foreign key(deptno) references dept(deptno) disable novalidate;
alter table emp modify constraint emp_fk_dept rely;

Odd!

Anyway, first, we can show a clear demonstration of JOIN ELIMINATION.

No FK constraint:

alter table emp drop constraint emp_fk_dept;

create or replace view emp_dept
as
select emp.ename, dept.dname
from   emp, dept
where  emp.deptno = dept.deptno; 

select ename from emp_dept;
select * from table(dbms_xplan.display_cursor);

Gives plan:

Plan hash value: 4269077325
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       | 21974 (100)|          |
|   1 |  NESTED LOOPS      |         |  1000K|    31M| 21974   (1)| 00:04:24 |
|   2 |   TABLE ACCESS FULL| EMP     |  1000K|    19M| 21924   (1)| 00:04:24 |
|*  3 |   INDEX UNIQUE SCAN| DEPT_PK |     1 |    13 |     0   (0)|          |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Now with added constraint, RELY DISABLE:

alter table emp add constraint emp_fk_dept foreign key(deptno) references dept(deptno) disable novalidate;
alter table emp modify constraint emp_fk_dept rely;

select ename from emp_dept;
select * from table(dbms_xplan.display_cursor);

And we get:

Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       | 21925 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  | 50000 |   976K| 21925   (1)| 00:04:24 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
    1 - filter("EMP"."DEPTNO" IS NOT NULL)

And just to confirm our constraint state:

select constraint_name, status, validated, rely from user_constraints where constraint_name = 'EMP_FK_DEPT';
CONSTRAINT_NAME                STATUS   VALIDATED     RELY
------------------------------ -------- ------------- ----
EMP_FK_DEPT                    DISABLED NOT VALIDATED RELY 

Now we can also see benefit in MV query_rewrite:

create materialized view mv_emp_dept
enable query rewrite
as
select dept.deptno, dept.dname, count (*) 
from   emp, dept
where  emp.deptno = dept.deptno
group by dept.deptno, dept.dname;

begin
   dbms_stats.set_table_stats
   ( user, 'mv', numrows=>100000, numblks=>10000 );
end; 
/

alter session set query_rewrite_enabled = false;
select count(*) from emp;
select * from table(dbms_xplan.display_cursor);
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       | 21917 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |  1000K| 21917   (1)| 00:04:24 |
-------------------------------------------------------------------

Enable query_rewrite and we can use MV instead:

alter session set query_rewrite_enabled = true;
alter session set query_rewrite_integrity = trusted;
select count(*) from emp;
select * from table(dbms_xplan.display_cursor);
Plan hash value: 632580757
---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE               |             |     1 |    13 |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| MV_EMP_DEPT |     3 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Conditional uniqueness

A quick fly through the options for conditional uniqueness.

Requirement #1: I want uniqueness on a column but only under certain conditions.

For example, I have an active flag and I want to make sure there is only one active record for a particular attribute but there can be many inactive rows.

Initial setup:

create table t1
(col1      number       not null
,col2      varchar2(24) not null
,is_active number(1)    not null
,constraint pk_t1 primary key (col1)
,constraint ck_t1_is_active check (is_active in (1,0)));

Solution #1: A unique index on an expression which evaluates to null when the condition is not met.

create unique index i_t1 on t1 (case when is_active = 1 then col2 end);

unique index I_T1 created.

insert into t1 values(1,'SHAGGY',1);

1 rows inserted.

insert into t1 values(2,'SHAGGY',1);

SQL Error: ORA-00001: unique constraint (I_T1) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

Only one active SHAGGY allowed.
But multiple inactives allowed:

insert into t1 values(2,'SHAGGY',0);

1 rows inserted.

insert into t1 values(3,'SHAGGY',0);

1 rows inserted.

Solution #2: A virtual column with a unique constraint

drop index i_t1;

index I_T1 dropped.

alter table t1 add (vc_col2 varchar2(24) generated always as (case when is_active = 1 then col2 end));

table T1 altered.

alter table t1 add constraint uk_t1 unique (vc_col2);

table T1 altered.

Note that now we have a virtual column we have to be very aware of insert statements with no explicit column list:

insert into t1 values(4,'SCOOBY',1);

SQL Error: ORA-00947: not enough values
00947. 00000 -  "not enough values"

Unless we’re lucky enough to be on 12c and use the INVISIBLE syntax:

alter table t1 add (vc_col2 varchar2(24) invisible generated always as (case when is_active = 1 then col2 end));

But as this example is on 11.2.0.3:

insert into t1 (col1, col2, is_active) values(4,'SCOOBY',1);

1 rows inserted.

insert into t1 (col1, col2, is_active) values(5,'SCOOBY',1);

SQL Error: ORA-00001: unique constraint (UK_T1) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

insert into t1 (col1, col2, is_active) values(5,'SCOOBY',0);

1 rows inserted.

insert into t1 (col1, col2, is_active) values(6,'SCOOBY',0);

1 rows inserted.

Requirement #2: Sorry we forgot to tell you that we insert the new row first and the update the old one to be inactive so we need deferred constraint (hmmm!)

In which case, you can’t have deferred uniqueness on an index so the only option is the virtual column.

alter table t1 drop constraint uk_t1;

table T1 altered.

alter table t1 add constraint uk_t1 unique (vc_col2) deferrable initially deferred;

table T1 altered.

insert into t1 (col1, col2, is_active) values(7,'FRED',1);

1 rows inserted.

insert into t1 (col1, col2, is_active) values(8,'FRED',1);

1 rows inserted.

commit;

SQL Error: ORA-02091: transaction rolled back
ORA-00001: unique constraint (.UK_T1) violated
02091. 00000 -  "transaction rolled back"
*Cause:    Also see error 2092. If the transaction is aborted at a remote
           site then you will only see 2091; if aborted at host then you will
           see 2092 and 2091.
*Action:   Add rollback segment and retry the transaction.

insert into t1 (col1, col2, is_active) values(7,'FRED',1);

1 rows inserted.

insert into t1 (col1, col2, is_active) values(8,'FRED',1);

1 rows inserted.

update t1 set is_active = 0 where col1 = 7;

1 rows updated.

commit;

committed.

See previous post on similar approach for conditional foreign key

Follow

Get every new post delivered to your Inbox.

Join 75 other followers