Tuning Tips - Missing Foreign Key Indexes

Before discussing tuning tips on histograms lets cover some background

What is a histogram?

A Foreign Key (FK) are used in Relational Databases (RDBMS). When a Foreign Key is defined, it means that the values of the column must be in the values of the associated Primary Key table.

For Example, lets say we have a DEPARTMENT table and it looks like:

DEPARTMENT_ID , number
DEPARTMENT_NAME, varchar(64)

And we have an employee table that looks like:

EMP_ID number
EMP_NAME Varchar(64)
EMP_DEPARTMENT_ID number.

In this simple example EMP_DEPARTMENT_ID is a Foreign Key to the department table.

The department table's Primary Key is DEPARTMENT_ID.
The employee table has a Foreign Key EMP_DEPARTMENT_ID that references the department table's Primary Key is DEPARTMENT_ID. This simply means that values in the EMP_DEPARTMENT_ID column MUST BE IN the Department table DEPARTMENT_ID column.

When this is defined at the database level, the database is responsible for insuring no value is inserted or updated in the employee table that does not exist in the Department table. So the database is responsible for validating the relationship. This means the DB has to ensure that EMP_DEPARTMENT_ID column values in the Employee table are in the Department table DEPARTMENT_ID column.

How the database performs this enforcement and validation is documented for Oracle. Many people do not realize that the Oracle DB uses different methods to validate the Foreign Key relationships, a very fast method when the index exists and a slower on when the index does not exist.

If the Foreign Key index is not present, then the database has to use a method to valid the relationship. This can cause

1) Slower Performance.
2) Dead locks in the database.
3) Serial inserts and update of parent table in the relationships.
4) Slower OLTP Performance.

Some people may not care or feel that is ok to only allow one person to update any row if the department table. However the locking that happens on the department table structures, really serialized inserts and updates to the employee table. Meaning only one process at a time can insert or update the rows in the employee table. This can be fine for low active tables, but not for high OLTP tables like items in a shopping cart.

No one wants the order to be locked while items are being inserted into the shopping cart. Therefore the missing index has a direct impact on company revenue by slowing down the process of adding items to the shopping cart.

Ok, If the index is missing then we can see slower performance.

Other items that can cause index performance issues are

1) Multiple column in the PK - FK relationship.
If the PK is Multiple columns, then the FK has to be Multiple columns. Common error is where the Multiple column FK is not in the same order. When this happens the Database may not use the index to valid the FK and this results in slower performance.

2) Column data length difference
-- More about this on another tip page (coming soon)

3) Column type changes.
-- More about this on another tip page (coming soon)

We offer the Tuning Ace Nucleus part of the Tuning Ace Performance Tuning Suite. For more information please contact us here.