The Database Design Resource Center

Oracle Constraints and Indexing

Oracle automatically creates indexes, or uses existing indexes, on the keys and expressions of unique and primary keys that you define with integrity constraints.

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)
(CREATE INDEX emp_pk_idx ON employees(empno)));

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



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

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.