RELY Constraint

Learnt something newly from Oracle today. Its about RELY constraints. This RELY option tells oracle to trust (rely) on the data integrity


rajesh@ORA11GR2>
rajesh@ORA11GR2> create table emp as select * from scott.emp;

Table created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> create table dept as select * from scott.dept;

Table created.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'EMP');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'DEPT');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> create materialized view emp_mv
  2  build immediate
  3  refresh on demand
  4  enable query rewrite
  5  as
  6  select dname,count(*)
  7  from emp, dept
  8  where emp.deptno = dept.deptno
  9  group by dname
 10  /

Materialized view created.

Elapsed: 00:00:00.09
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace on explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*) from emp;

  COUNT(*)
----------
        14

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------

rajesh@ORA11GR2>
rajesh@ORA11GR2>

Materialized view is not used because Oracle doesn't understand the relationship between EMP and DEPT tables. Once we provide the relationship Oracle is able to perform query rewrite.

rajesh@ORA11GR2> alter table dept
  2  add constraint dept_pk
  3  primary key(deptno);

Table altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp
  2  add constraint emp_fk
  3  foreign key(deptno)
  4  references dept;

Table altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp modify deptno not null;

Table altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace on explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*) from emp;

  COUNT(*)
----------
        14

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1956236023


----------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |    13 |     3   (0)|
|   1 |  SORT AGGREGATE               |        |     1 |    13 |            |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| EMP_MV |     3 |    39 |     3   (0)|
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

rajesh@ORA11GR2>

Now, what happens in true data warehouse and there are millions of records in your fact tables? you really don't want the additional effort of verifying the Foreign key relationship- In which case you create a non-validate constraint- once that is used to inform about the relationship but that has not been validated by database.


rajesh@ORA11GR2> alter table emp
  2  drop constraint emp_fk
  3  /

Table altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table dept
  2  drop constraint dept_pk
  3  /

Table altered.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp modify deptno null;

Table altered.

Elapsed: 00:00:00.03
rajesh@ORA11GR2>

Inserted purposely a row into emp that violates the constraint to show the Impact of RELY constraints.


rajesh@ORA11GR2> insert into emp(empno,deptno) values(1,1);

1 row created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table dept
  2  add constraint dept_pk
  3  primary key(deptno)
  4  RELY enable novalidate;

Table altered.

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp
  2  add constraint emp_fk
  3  foreign key(deptno)
  4  references dept
  5  RELY enable novalidate;

Table altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

The NOVALIDATE bypasses the checking of the existing data & RELY tells Oracle to "trust" the data integrity.

rajesh@ORA11GR2> alter table emp modify deptno not null novalidate;

Table altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

rajesh@ORA11GR2> exec dbms_mview.refresh('EMP_MV');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29
rajesh@ORA11GR2>
rajesh@ORA11GR2>

rajesh@ORA11GR2> show parameter query_rewrite_inte;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
query_rewrite_integrity              string      enforced
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace on explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*) from emp;

  COUNT(*)
----------
        15

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------

rajesh@ORA11GR2>

Enforced - This is the default mode. The optimizer only uses fresh data from the materialized views and only use those relationships that are based on ENABLED VALIDATED primary, unique, or foreign key constraints.

Oracle did not rewrite the query to use materialized view because of this. We must go down a level in query integrity to inform oracle to "trust" us.

<quote>
In TRUSTED mode, the optimizer trusts that the relationships declared in dimensions and RELY constraints are correct. In this mode, the optimizer also uses prebuilt materialized views or materialized views based on views, and it uses relationships that are not enforced as well as those that are enforced. In this mode, the optimizer also trusts declared but not ENABLED VALIDATED primary or unique key constraints and data relationships specified using dimensions. This mode offers greater query rewrite capabilities but also creates the risk of incorrect results if any of the trusted relationships you have declared are incorrect.
</quote>

rajesh@ORA11GR2> alter session set query_rewrite_integrity=trusted;

Session altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace on explain;
rajesh@ORA11GR2> select count(*) from emp;

  COUNT(*)
----------
        14

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1956236023


-----------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |    13 |     3   (0)|
|   1 |  SORT AGGREGATE               |        |     1 |    13 |            |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| EMP_MV |     3 |    39 |     3   (0)|
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

rajesh@ORA11GR2>

Here Oracle did the query rewrite the side effect is that in this case wrong answer is returned. The reason we got the wrong answer is when the MV is refreshed, It didnot get the newly added row. The data we told oracle to "rely" was not at all reliable.

Be 100% sure that your data is reliable when you ask oracle to "rely" on it.

In short,

RELY ENABLE NOVALIDATE and ENABLE NOVALIDATE - Both enable contraint but dont validate existing data.

RELY ENABLE NOVALIDATE - Will peform query rewrite if query_rewrite_integrity=trusted.
ENABLE NOVALIDATE - Will **not** peform query rewrite if query_rewrite_integrity=trusted/enforced/stale_tolerated

RELY ENABLE NOVALIDATE - May yeild wrong results in answering query. So we have to be sure that data is 100% pure when defining RELY option