Say you have a requirement that every record in the ADDRESS table be assigned a corresponding CUST_ID column from the CUST table. To enforce this relationship, you create the ADDRESS table and a foreign key constraint, as follows:

SQL> create table address(address_id number ,cust_address varchar2(2000),cust_id number); —

SQL> alter table address add constraint addr_fk1 foreign key (cust_id) references cust(cust_id);

Note a foreign key column must reference a column in the parent table that has a primary key or unique key constraint defined on it. Otherwise, you will receive the error ORA-02270: no matching unique or primary key for this column-list.

You realize that the foreign key column is used extensively when joining the CUST and ADDRESS tables and that an index on the foreign key column will increase performance. In this situation, you have to create an index manually. For instance, a regular B-tree index is created on the foreign key column of CUST_ID in the ADDRESS table.

SQL> create index addr_fk1 on address(cust_id);

You do not have to name the index the same name as the foreign key (as I did in these lines of code). It is a personal preference as to whether you do that. I feel it is easier to maintain environments when the constraint and corresponding index have the same name.

When creating an index, if you do not specify the tablespace name, Oracle places the index in the user’s default tablespace. It is usually a good idea to explicitly specify which tablespace the index should be placed in; for example,

SQL> create index addr_fk1 on address(cust_id) tablespace reporting_index;

Determining Whether Foreign Key Columns Are Indexed

If you are creating an application from scratch, it is fairly easy to create the code and ensure that each foreign key constraint has a corresponding index. However, if you have inherited a database, it is prudent to check if the foreign key columns are indexed.

You can use data dictionary views to verify if all columns of a foreign key constraint have a corresponding index. The task is not as simple as it might first seem. For example, here is a query that gets you started in the right direction:

SQL> SELECT DISTINCT

a.owner              owner

,a.constraint_name cons_name

,a.table_name ,tab_name

b.column_name cons_column

NVL(c.column_name,’***Check index****’) ind_column FROM dba_constraints a,dba_cons_columns b ,dba_ind_columns c

WHERE constraint_type = ‘R’

AND a.owner = UPPER(‘&&user_name’) AND a.owner = b.owner

AND a.constraint_name = b.constraint_name AND b.column_name = c.column_name(+)

AND b.table_name = c.table_name(+) AND b.position = c.column_position(+) ORDER BY tab_name, ind_column;

This query, while simple and easy to understand, does not correctly report on unindexed foreign keys for all situations.

For example, in the case of multicolumn foreign keys, it does not matter if the constraint is defined in an order different from that of the index columns, as long as the columns defined in the constraint are in the leading edge of the index.

In other words, for a constraint on (COL1, COL2), an index will work on either (COL1,COL2) or (COL2,COL1);the order of the same set of columns doesn’t matter. Also, an index on (COL1,COL2,COL3) will also work, because the extra index column is OK as long as the leading columns match.

SQL> create index column_test_idx on table1 (col2, col1);

SQL> create index column_test_idx on table1 (col1, col2, col3);

Another issue is that a B-tree index protects you from locking issues, but a bitmap index does not. In this situation, the query should also check the index type.

In these scenarios, you will need a more sophisticated query to detect indexing issues related to foreign key columns. The query checks index type and finds the related tables and their indexes to show what the index columns are with the tables and indexes.

The following example is a more complex query that uses the LISTAGG analytic function to compare columns (returned as a string in one row) in a foreign key constraint with corresponding indexed columns:

SQL> SELECT

CASE WHEN ind.index_name IS NOT NULL THEN CASE WHEN ind.index_type IN (‘BITMAP’) THEN ‘** Bitmp idx **’

ELSE ‘indexed’ END

ELSE

‘** Check idx **’ END checker ,ind.index_type,cons.owner, cons.table_name, ind.index_name, cons.constraint_name, cons.cols

FROM (SELECT

c.owner, c.table_name, c.constraint_name

, LISTAGG(cc.column_name, ‘,’ ) WITHIN GROUP (ORDER BY cc.column_name) cols FROM dba_constraints c

,dba_cons_columns cc WHERE c.owner = cc.owner

AND c.owner = UPPER(‘&&schema’)

AND c.constraint_name = cc.constraint_name AND c.constraint_type = ‘R’

GROUP BY c.owner, c.table_name, c.constraint_name) cons LEFT OUTER JOIN

(SELECT

table_owner, table_name, index_name, index_type, cbr ,LISTAGG(column_name, ‘,’ ) WITHIN GROUP (ORDER BY column_name) cols FROM (SELECT

ic.table_owner, ic.table_name, ic.index_name

,ic.column_name, ic.column_position, i.index_type ,CONNECT_BY_ROOT(ic.column_name) cbr

FROM dba_ind_columns ic ,dba_indexes i

WHERE ic.table_owner = UPPER(‘&&schema’) AND ic.table_owner = i.table_owner

AND ic.table_name = i.table_name AND ic.index_name = i.index_name

CONNECT BY PRIOR ic.column_position-1 = ic.column_position AND PRIOR ic.index_name = ic.index_name)

GROUP BY table_owner, table_name, index_name, index_type, cbr) ind ON cons.cols = ind.cols

AND cons.table_name = ind.table_name AND cons.owner = ind.table_owner

ORDER BY checker, cons.owner, cons.table_name;

This query will prompt you for a schema name and then will display all foreign key constraints that do not have corresponding indexes. This query also checks for the index type; as previously stated, bitmap indexes may exist on foreign key columns but do not prevent locking issues.