Database Constraints

Some versions of Oracle have a bug that limits performance and concurrency when an operation uses a column that has a foreign key constraint and that column is also not indexed. Such columns can cause deadlocks and performance problems.

For systems that use foreign keys extensively, this leads to a trade-off that must be made between performance, storage, and use of foreign keys. You can use the set system constraint command to tailor the schema for one of three possible modes of operation: 

set system constraint |none                     |;
                      |index [indexspace SPACE] |
                      |normal                   |

Where:

Keyword Description
normal The normal system setting results in a schema that has foreign keys that are not indexed. This is the default setting. Databases using this setting are subject to the Oracle concurrency bug regarding non-indexed foreign keys.
index The index setting results in an Oracle index being added to every column that is defined as a foreign key, and has no existing index. Overhead in both storage and performance is added since updates to foreign keys also require updates to their corresponding index. This option should be used in development and test environments, where there is substantial benefit in the enforcement of foreign key constraints, and the negative storage/performance impact will not affect large numbers of users.

When issuing the command to add indices to the system, you can specify the tablespace to use for the indexing operation.

none This option improves concurrency by removing non-indexed foreign key constraints, and no additional Oracle index is required. This option eliminates the concurrency problem of foreign keys, and in fact, further improves system performance and scalability by eliminating the low-level integrity checks performed at the database level. This option should be used once an application has been thoroughly tested and is rolled out to a large-scale production environment.

When the system is set with a constraint mode, not only are the changes made to the relevant columns, but also the setting is stored in the database so that all future operations including creation of new tables, running the index vault command, and upgrade will use the selected mode. These options only affect approximately a dozen columns (among all tables) that have a foreign constraint but not an index.