Oracle Constraints and Indexing

Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables.

Such an index allows UPDATEs and DELETEs on the parent table without share locking the child table.

You might want to experiment by comparing the processing times of the SQL statements with and without indexes. You can measure processing time with the SQL trace facility.

SQL> SELECT constraint_name
2 FROM user_constraints
3 WHERE table_name = 'TEST'
4 AND constraint_type = 'P';

SQL> SELECT index_name, column_name
2 FROM user_ind_columns
3 WHERE table_name = 'TEST';

In Oracle the index used to support Primary and Unique keys can be defined independently of the constraint itself by using the CREATE INDEX syntax within the USING INDEX clause of the CREATE TABLE statement:

CREATE TABLE employees(
empno NUMBER(6),
name VARCHAR2(30),
dept_no NUMBER(2),
CONSTRAINT emp_pk primary key(empno)
USING INDEX
(CREATE INDEX emp_pk_idx ON employees(empno)));

The constraint can subsequently be dropped without dropping the index using either syntax:

ALTER TABLE employees DROP PRIMARY KEY KEEP INDEX;

ALTER TABLE employees DROP CONSTRAINT empno_pk;

Share Locks On Unindexed FKs

Due to above defined feature of indexing with constraints on foreign key, we can issue a share lock on the entire child table while the parent table was being updated if the foreign key between them was unindexed.

This had the affect of preventing DML operations on the child table until the parent table transaction was complete. In Oracle this situation has been altered such that a table level share lock is issued and instantly released.

Constraints on Function Based Indexes:

Function Based Indexes are now capable of doing an index-only scan. In previous versions this was only possible if NULL values were explicitly prevented by the index creation statement.

Since each built-in operator knows implicitly whether it can produce null values when all it's input parameters are not null, Oracle can deduce if nulls can be produced and therefore decide if index-only scans are possible based on the columns queried using the function based index.

Using Nonunique Indexes to Enforce Uniqueness

You can use an existing nonunique index on a table to enforce uniqueness, either for UNIQUE constraints or the unique aspect of a PRIMARY KEY constraint.

The advantage of this approach is that the index remains available and valid when the constraint is disabled. Therefore, enabling a disabled UNIQUE or PRIMARY KEY constraint does not require rebuilding the unique index associated with the constraint.

This can yield significant time savings on enable operations for large tables.

Using a nonunique index to enforce uniqueness also lets you eliminate redundant indexes. You do not need a unique index on a primary key column if that column already is included as the prefix of a composite index. You can use the existing index to enable and enforce the constraint.

You also save significant space by not duplicating the index. However, if the existing index is partitioned, then the partitioning key of the index must also be a subset of the UNIQUE key.

Otherwise oracle creates an additional unique index to enforce the constraint.

Using Enabled Novalidated Constraints for indexing

An enabled novalidated constraint behaves similarly to an enabled validated constraint for new data.

Placing a constraint in the enabled novalidated state signifies that any new data entered into the table must conform to the constraint.

Existing data is not checked. By placing a constraint in the enabled novalidated state, you enable the constraint without locking the table.

If you change a constraint from disabled to enabled, then the table must be locked. No new DML, queries, or DDL can occur, because there is no mechanism to ensure that operations on the table conform to the constraint during the enable operation.

The enabled novalidated state prevents operations violating the constraint from being performed on the table.

An enabled novalidated constraint can be validated with a parallel, consistent-read query of the table to determine whether any data violates the constraint. No locking is performed, and the enable operation does not block readers or writers to the table. In addition, enabled novalidated constraints can be validated in parallel.

Multiple constraints can be validated at the same time and each constraint's validity check can be determined using parallel query.

Return to Oracle Database constraints