Home » RDBMS Server » Performance Tuning » RELY constraints (DB 12.x)
RELY constraints [message #674601] Tue, 05 February 2019 04:55
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
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.
Previous Topic: driving table and join order
Next Topic: Table Order in Join
Goto Forum:
  


Current Time: Sat Dec 07 06:00:52 CST 2019