Unique Constraint Indexes

It’s generally known by developers that when creating unique constraint Oracle will create a unique index to enforce the constraint, as illustrated below:

SQL> CREATE TABLE tab
  2     (col1     NUMBER (10) NOT NULL)
  3  /

Table created.

SQL> ALTER TABLE tab
  2     ADD CONSTRAINT tab_u1
  3     UNIQUE (col1)
  4  /

Table altered.

SQL> SELECT index_name
  2  ,      uniqueness
  3  FROM   user_indexes
  4  WHERE  table_name = 'TAB'
  5  /


INDEX_NAME           UNIQUENESS
-------------------- --------------------
TAB_U1               UNIQUE

For a unique constraint we can see what index is enforcing the constraint by querying USER_CONSTRAINTS:

SQL>SELECT constraint_name
  2  ,      index_name
  3  FROM   user_constraints
  4  WHERE  constraint_name = 'TAB_U1'
  5  /

CONSTRAINT_NAME      INDEX_NAME
-------------------- --------------------
TAB_U1               TAB_U1

When the index is created as part of adding the constraint then the index will be given the same name as the constraint, as shown in the example above. (As an aside, all these examples have been done on a 12.1.0.2 database but the details will be the same in earlier versions.)

What is less generally known about uniquness constraints (or primary key constraints for that matter) is that the constraint may be enforced by a non-unique index. This is done by creating the index first and then specifying the index to be used when creating the constraint, as shown below:

SQL>CREATE TABLE tab
  2     (col1     NUMBER (10) NOT NULL)
  3  /

Table created.

SQL>CREATE INDEX tab_i1
  2     ON tab (col1)
  3  /

Index created.

SQL>ALTER TABLE tab
  2     ADD CONSTRAINT tab_u1
  3     UNIQUE (col1)
  4     USING INDEX tab_i1
  5  /

Table altered.

SQL>SELECT index_name
  2  ,      uniqueness
  3  FROM   user_indexes
  4  WHERE  table_name = 'TAB'
  5  /

INDEX_NAME           UNIQUENESS
-------------------- -------------------
TAB_I1               NONUNIQUE

SQL>SELECT constraint_name
  2  ,      index_name
  3  FROM   user_constraints
  4  WHERE  constraint_name = 'TAB_U1'
  5  /

CONSTRAINT_NAME      INDEX_NAME
-------------------- -----------------
TAB_U1               TAB_I1

The index selected to enforce the constraint needs to lead with the same columns as the constraint, although the columns can be specified in any order:

SQL>CREATE TABLE tab
  2     (col1     NUMBER (10) NOT NULL
  3     ,col2     NUMBER (10) NOT NULL
  4     ,col3     NUMBER (10) NOT NULL)
  5  /

Table created.

SQL>
SQL>CREATE INDEX tab_i1
  2     ON tab (col2, col1)
  3  /

Index created.

SQL>
SQL>ALTER TABLE tab
  2     ADD CONSTRAINT tab_u1
  3     UNIQUE (col1, col2)
  4     USING INDEX tab_i1
  5  /

Table altered.

Failure to specify an index that leads with all the columns of the unique constraint results in the error:

ORA-14196: Specified index cannot be used to enforce the constraint.

Knowing the above gives us some opportunity to optimise our applications. We are able to create indexes that support the unique constraint but can also be tweaked to support other processes too. A relatively common scenario involves the need to look up a unique value in a table in order to obtain another attribute from the table. This might be obtaining the description associated with a unqiue ID value or, in a data warehouse scenario, this might be to lookup a dimension surrogate key from the source system unique key. Rather than create two indexes, one to support the unique constraint and another to aid our lookup, we can create just the latter one and use it to enforce the unique constraint too as the following example illustrates:

SQL>CREATE TABLE dim_x
  2     (dim_pk       NUMBER (10) NOT NULL
  3     ,source_pk    NUMBER (10) NOT NULL)
  4  /

Table created.

SQL>CREATE INDEX dim_x_i1
  2     ON dim_x (source_pk, dim_pk)
  3  /

Index created.

SQL>ALTER TABLE dim_x
  2     ADD CONSTRAINT dim_x_u1
  3     UNIQUE (source_pk)
  4     USING INDEX dim_x_i1
  5  /

Table altered.

Rather than needing to perform an index lookup followed by a ROWID access into our table Oracle is able to perform the lookup from the index alone:

SQL>INSERT INTO dim_x
  2  SELECT ROWNUM
  3  ,      ROWNUM+ 99
  4  FROM   dual
  5  CONNECT BY ROWNUM 
SQL>COMMIT
  2  /

Commit complete.

SQL>
SQL>EXEC dbms_stats.gather_table_stats ('','dim_x')

PL/SQL procedure successfully completed.

SQL>EXPLAIN PLAN
  2  FOR
  3  SELECT dim_pk
  4  FROM   dim_x
  5  WHERE  source_pk = :source_pk
  6  /

Explained.

SQL>
SQL>SELECT *
  2  FROM   TABLE(dbms_xplan.display())
  3  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
Plan hash value: 3680186242

------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     8 |     2   (0)|
|*  1 |  INDEX RANGE SCAN| DIM_X_I1 |     1 |     8 |     2   (0)|
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("SOURCE_PK"=TO_NUMBER(:SOURCE_PK))

13 rows selected.

As with many tips, check carefully first as there are trade-offs to consider. The optimiser treats unique and non-unique indexes differently so check that the benefit you’re expecting from the index change is apparent and that hasn’t introduced any side-effects.

Advertisement