SQL Tuning: Find Unindexed Foreign Keys
Foreign key (FK) constraints without supporting indexes can cause severe performance problems during SQL statements. Caché Monitor can detect unindexed foreign key constraints for you.
Reasons for Foreign Key Constraints
Creating foreign key constraints on tables increases the integrity of data by preventing rows from being inserted into detail table that do not have a matching row in a master table. But the FK constraint declaration (with DDL) in Caché does not automatically create a supporting index. Relationships created via wizard are indexed by default.
Unindexed FK constraints can often lead to poor performance because the FK constraint represents a relation between tables, and if these tables are part of a SQL query, there’s a good chance that they will be joined on the constraint column. In this case you need an index that supports SQL queries.
Caché Monitor can do this job and detect unindexed foreign key constraints.