G-1240
🆓Warning
Try to index foreign key columns.
Reason
Creating a foreign key index improves performance by speeding up DELETE and primary key UPDATE operations on the parent table, preventing full table scans on the child table or locking the child table on UPDATE. It also speeds up foreign key access on the child table, which also improves join performance.
Example
Non-Compliant Example
create table parent ( parent_id raw(16) default sys_guid() not null, parent_name varchar2(100 char) not null, constraint parent_pk primary key (parent_id) ); create table child ( child_id raw(16) default sys_guid() not null, parent_id raw(16) not null, child_name varchar2(100 char) not null, constraint child_pk primary key (child_id), constraint child_parent_fk foreign key (parent_id) references parent(parent_id) );
Compliant Solution - ★★★★★
create table parent ( parent_id raw(16) default sys_guid() not null, parent_name varchar2(100 char) not null, constraint parent_pk primary key (parent_id) ); create table child ( child_id raw(16) default sys_guid() not null, parent_id raw(16) not null, child_name varchar2(100 char) not null, constraint child_pk primary key (child_id), constraint child_parent_fk foreign key (parent_id) references parent(parent_id) ); create index DBL_OWNER.CHILD_PARENT_FK_I on DBL_OWNER.CHILD(PARENT_ID);
Tests
Test SQL query
with
function create_fk_index_stmt (
in_owner in varchar2,
in_table_name in varchar2,
in_constraint_name in varchar2
) return clob is
co_templ constant varchar2(200 char) := 'create index #IF_NOT_EXISTS# #OWNER#.#INDEX_NAME# on #OWNER#.#TABLE_NAME#(#COLS#);';
l_stmt varchar2(4000 byte) := co_templ;
l_cols varchar2(4000 byte);
begin
if dbms_db_version.version >= 23 then
l_stmt := replace(l_stmt, '#IF_NOT_EXISTS#', 'if not exists');
else
l_stmt := replace(l_stmt, '#IF_NOT_EXISTS#', null);
end if;
l_stmt := replace(l_stmt, '#OWNER#', in_owner);
l_stmt := replace(l_stmt, '#INDEX_NAME#', in_constraint_name || '_I');
l_stmt := replace(l_stmt, '#TABLE_NAME#', in_table_name);
select listagg(column_name, ', ') within group (order by position)
into l_cols
from dba_cons_columns
where owner = in_owner
and table_name = in_table_name
and constraint_name = in_constraint_name;
l_stmt := replace(l_stmt, '#COLS#', l_cols);
return l_stmt;
end create_fk_index_stmt;
select c.owner || '.' || c.constraint_name || '.' || cc.column_name as identifier,
'Column ' || cc.column_name || ' in the foreign key constraint ' || c.constraint_name
|| ' of table ' || c.owner || '.' || c.table_name || ' is not indexed in the order of the constraint.' as message,
create_fk_index_stmt(c.owner, c.table_name, c.constraint_name) as migration
from dba_constraints c
join dba_cons_columns cc
on cc.owner = c.owner
and cc.constraint_name = c.constraint_name
and cc.table_name = c.table_name
left join dba_ind_columns ic
on ic.table_owner = c.owner
and ic.table_name = c.table_name
and ic.column_name = cc.column_name
and ic.column_position = coalesce(cc.position, 1)
where c.constraint_type = 'R'
and c.status = 'ENABLED'
and ic.column_name is null
and c.owner in (#SchemaNames#)
order by c.owner, c.constraint_name, c.table_name, cc.position, cc.column_nameTest results
| Identifier | Message | Migration |
|---|---|---|
| DBL_OWNER.CHILD_PARENT_FK.PARENT_ID | Column PARENT_ID in the foreign key constraint CHILD_PARENT_FK of table DBL_OWNER.CHILD is not indexed in the order of the constraint. | create index if not exists DBL_OWNER.CHILD_PARENT_FK_I on DBL_OWNER.CHILD(PARENT_ID); |
Parameters
Use parameters to customize the rule to your needs.
| Parameter | Description | Default Value |
|---|---|---|
| SchemaNames | Comma separated list of database schemas owning the database objects of an application. | dbl_owner |