This script works on foreign key constraints that have up to eight columns in them (if you have more than that, you probably want to rethink your design). It starts by building an inline view named CONS in the previous query.
This inline view transposes the appropriate column names in the constraint from rows into columns, with the result being a row per constraint and up to eight columns that have the names of the columns in the constraint.
Additionally, there is a column, COL_CNT, which contains the number of columns in the foreign key constraint itself. For each row returned from the inline view, we execute a correlated subquery that checks all of the indexes on the table currently being processed. It counts the columns in that index that match columns in the foreign key constraint and then groups them by index name.
So, it generates a set of numbers, each of which is a count of matching columns in some index on that table. If the original COL_CNT is greater than all of these numbers, then there is no index on that table that supports that constraint. If COL_CNT is less than all of these numbers, then there is at least one index that supports that constraint.
Note the use of the NVL2 function, which we used to “glue” the list of column names into a comma-separated list. This function takes three arguments: A, B, C. If argument A is not null, then it returns argument B; otherwise, it returns argument C. This query assumes that the owner of the constraint is the owner of the table and index as well. If another user indexed the table or the table is in another schema (both rare events), it will not work correctly.
The prior script also checks to see if the index type is a BTree index (NORMAL or NORMAL/REV). We’re checking to see if it’s a BTree index because a bitmap index on a foreign key column does not prevent the locking issue.
Note In data warehouse environments, it’s common to create bitmap indexes on a fact table’s foreign key columns. However, in data warehouse environments, usually the loading of data is done in an orderly manner through scheduled ETL processes and, therefore, would not encounter the situation of inserting into a child table as one process while concurrently deleting from a parent table from another process (like you might encounter in an OLTP application).
So, the prior script shows that table C has a foreign key on the column X but no index. By creating a B*Tree index on X, we can remove this locking issue all together. In addition to this table lock, an unindexed foreign key can also be problematic in the following cases:
•\ When you have an ON DELETE CASCADE and have not indexed the child table. For example, EMP is a child of DEPT. DELETE DEPTNO = 10 should CASCADE to EMP. If DEPTNO in EMP is not indexed, you will get a full table scan of EMP for each row deleted from the DEPT table. This full scan is probably undesirable, and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.
•\ When you query from the parent to the child. Consider the EMP/DEPT example again. It is very common to query the EMP table in the context of a DEPTNO. If you frequently run the following query (say, to generate a report), you’ll find that not having the index in place will
slow down the queries:
select * from dept, emp where emp.deptno = dept.deptno and dept.deptno = :X;
When do you not need to index a foreign key? The answer is, in general, when the following conditions are met:
•\ You do not delete from the parent table.
•\ You do not update the parent table’s unique/primary key value(watch for unintended updates to the primary key by tools).
•\ You do not join from the parent to the child (like DEPT to EMP).
If you satisfy all three conditions, feel free to skip the index; it’s not needed. If you meet any of the preceding conditions, be aware of the consequences. This is the one rare instance when Oracle tends to overlock data.
Leave a Reply