Databases Reference
In-Depth Information
For example,
create index addr_fk1
on address(cust_id)
tablespace reporting_index;
Note
An index on foreign key columns doesn't have to be of type B-tree. In data warehouse environments, it's
common to use bitmap indexes on foreign key columns in star schema fact tables. Unlike B-tree indexes, bitmap
indexes on foreign key columns don't resolve parent/child table locking issues. Applications that use star schemas
typically are not deleting or modifying the child record from fact tables; therefore locking is less of an issue in data
warehouse environments that use bitmap indexes on foreign key columns.
Determining if Foreign Key Columns are Indexed
If you're creating an application from scratch, it's fairly easy to create the code and ensure that each
foreign key constraint has a corresponding index. However, if you've inherited a database, it's 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 basic idea is to check each foreign key constraint to see if there is a
corresponding index. The task isn't as simple as it might first seem. For example, here's a query that gets
you started in the right direction:
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, doesn't correctly report on un-indexed foreign
keys for all situations. For example, in the case of multi-column foreign keys, it doesn't matter if the
constraint is defined in a different order from the index columns, as long as the indexed columns are in
the leading edge of the index. In other words, if the constraint is defined to be
COL1
and
COL2
, then it's
okay to have a B-tree index defined on leading edge
COL2
and then
COL1
.
