No Index Option
The NO INDEX option indicates that no secondary index is created to support the constraint. This option is permissible for referential constraints only and results in no index being available to check the integrity of deletes and updates to the referenced table. The database procedures that perform the integrity checks still execute in the absence of these indexes. The query plan, however, may use some other user-defined index on the same columns; or it may resort to a full table scan of the referencing table, if there is no alternative.
To avoid poor performance, the NO INDEX option must be used only if:
• An alternate index on referencing columns is available
• There are very few rows in the referencing table (as in a prototype application)
• Deletes and updates are rarely (if ever) performed on the referenced table