Check all foreign key references have a full index on both sides of the constraint
Problem
I found an article online that explains why it's important to have indexes for all foreign key constraints:
Even though Oracle allows you to define a foreign key without an index, it's the SQL workload that determines whether an index is required. A "missing" foreign key index will manifest itself with unnecessary large-table full-table scans against the child table.
Without an index on the child table's foreign key, table-level locking may occur when a parent row is updated. If you fail to create such a foreign key index and if the parent table is subject to updates, you may see heavy lock contention.
If ever in doubt, it's often safer to create indexes on ALL foreign keys, despite the possible overhead of maintaining unneeded indexes.
If indexes are missing from either side of a foreign key constraint, it can have a significant impact on DB performance.
I checked this with Nilo and he verified that this is still the case for our current Oracle version 19.
TO DO
All foreign key constraints are listed below. Check the CTA Catalogue schema to make sure that all the required indexes are in place.
-
CONSTRAINT DISK_INSTANCE_SPACE_DIN_FK FOREIGN KEY(DISK_INSTANCE_NAME) REFERENCES DISK_INSTANCE(DISK_INSTANCE_NAME) -
CONSTRAINT DISK_SYSTEM_DIN_DISN_FK FOREIGN KEY(DISK_INSTANCE_NAME, DISK_INSTANCE_SPACE_NAME) REFERENCES DISK_INSTANCE_SPACE(DISK_INSTANCE_NAME, DISK_INSTANCE_SPACE_NAME) -
CONSTRAINT STORAGE_CLASS_VOI_FK FOREIGN KEY(VIRTUAL_ORGANIZATION_ID) REFERENCES VIRTUAL_ORGANIZATION(VIRTUAL_ORGANIZATION_ID) -
CONSTRAINT TAPE_POOL_VO_FK FOREIGN KEY(VIRTUAL_ORGANIZATION_ID) REFERENCES VIRTUAL_ORGANIZATION(VIRTUAL_ORGANIZATION_ID) -
CONSTRAINT ARCHIVE_ROUTE_STORAGE_CLASS_FK FOREIGN KEY(STORAGE_CLASS_ID) REFERENCES STORAGE_CLASS(STORAGE_CLASS_ID), -
CONSTRAINT ARCHIVE_ROUTE_TAPE_POOL_FK FOREIGN KEY(TAPE_POOL_ID) REFERENCES TAPE_POOL(TAPE_POOL_ID), -
CONSTRAINT TAPE_LOGICAL_LIBRARY_FK FOREIGN KEY(LOGICAL_LIBRARY_ID) REFERENCES LOGICAL_LIBRARY(LOGICAL_LIBRARY_ID), -
CONSTRAINT TAPE_TAPE_POOL_FK FOREIGN KEY(TAPE_POOL_ID) REFERENCES TAPE_POOL(TAPE_POOL_ID), -
CONSTRAINT TAPE_MEDIA_TYPE_FK FOREIGN KEY(MEDIA_TYPE_ID) REFERENCES MEDIA_TYPE(MEDIA_TYPE_ID) -
CONSTRAINT RQSTER_ACT_RULE_MNT_PLC_FK FOREIGN KEY(MOUNT_POLICY_NAME) -
CONSTRAINT RQSTER_RULE_MNT_PLC_FK FOREIGN KEY(MOUNT_POLICY_NAME) -
CONSTRAINT RQSTER_GRP_RULE_MNT_PLC_FK FOREIGN KEY(MOUNT_POLICY_NAME) -
CONSTRAINT ARCHIVE_FILE_STORAGE_CLASS_FK FOREIGN KEY(STORAGE_CLASS_ID) REFERENCES STORAGE_CLASS(STORAGE_CLASS_ID), -
CONSTRAINT TAPE_FILE_TAPE_FK FOREIGN KEY(VID) -
CONSTRAINT TAPE_FILE_ARCHIVE_FILE_FK FOREIGN KEY(ARCHIVE_FILE_ID) -
CONSTRAINT FILE_RECYCLE_LOG_VID_FK FOREIGN KEY(VID) REFERENCES TAPE(VID), -
CONSTRAINT FILE_RECYCLE_LOG_SC_FK FOREIGN KEY(STORAGE_CLASS_ID) REFERENCES STORAGE_CLASS(STORAGE_CLASS_ID)