RELY DISABLE
December 5, 2014 2 Comments
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.
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)); alter table dept add constraint dept_pk primary key (deptno); 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); alter table emp add constraint emp_pk primary key (empno); 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 | ---------------------------------------------------------------------------------------------
I changed the title because I didn’t want to give the misleading impression that RELY overrules DISABLE.
Thanks Dom.. Quite interesting that how constraints in RELY mode help optimizer
Regards,
Harman