I'm trying to work up some examples of using RELY constraints, without success. For example, check out this:orclx>
orclx> set autot trace exp
orclx> select distinct ename from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3709190377
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 84 | 4 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 14 | 84 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 84 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
orclx>
Oracle scans the table, then removes duplicates. I expected that if I tell Oracle that ename is unique, the CBO would then not bother with operation 1 because it would assume no duplicates. But it doesn't:orclx> alter table emp add constraint nameuk unique (ename) rely disable novalidate;
Table altered.
orclx> select distinct ename from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3709190377
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 84 | 4 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 14 | 84 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 84 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
orclx>
Declaring ename not null does not make any difference. The only way I can avoid the HASH UNIQUE is by creating the constraint as enabled, thus creating a unique index, and declaring it not null:orclx> alter table emp modify (ename not null);
Table altered.
orclx> alter table emp add constraint nameuk unique (ename);
Table altered.
orclx> select distinct ename from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 928763176
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 84 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | NAMEUK | 14 | 84 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
orclx>
and if I hide the index, I'm back to the old plan:orclx> alter index nameuk invisible;
Index altered.
orclx> select distinct ename from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3709190377
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 84 | 4 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 14 | 84 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 84 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
orclx>
Does anyone happen to have a simple example of a RELY constraint in action? Or can explain what it is that I am misunderstanding?
Thankyou for any insight.