Locks and Foreign Keys
Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. In heap-organized tables, locking behavior depends on the indexing of foreign key columns. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason, Oracle recommends indexing foreign keys in most cases except when the matching unique or primary key is never updated or deleted. -link
vi check_fk_witout_index.sql
note: you must be connect as schema
note: The owner is pick up from schema has been connect
col TABLE_NAME format a30
col CONSTRAINT_NAME format a30
select uc.table_name, uc.constraint_name
from user_constraints uc
where uc.constraint_type='R'
and exists
(select ucc.position, ucc.column_name
from user_cons_columns ucc
where ucc.constraint_name=uc.constraint_name
minus
select uic.column_position as position, uic.column_name
from user_ind_columns uic
where uic.table_name=uc.table_name);
vi check_fk_witout_index_type_schema_name.sql
note: check in dba views, you have to tape owner
col TABLE_NAME format a30
col CONSTRAINT_NAME format a30
select uc.table_name, uc.constraint_name
from dba_constraints uc
where uc.owner='&owner' and uc.constraint_type='R'
and exists
(select ucc.position, ucc.column_name
from dba_cons_columns ucc
where ucc.constraint_name=uc.constraint_name and ucc.owner=uc.owner
minus
select uic.column_position as position, uic.column_name
from dba_ind_columns uic
where uic.table_name=uc.table_name and uic.table_owner=uc.owner);
To see constraints within schema
select TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME, CONSTRAINT_TYPE, DELETE_RULE from dba_constraints where owner='SCOTT';
TABLE_NAME R_OWNER R_CONSTRAINT_NAME C DELETE_RU
------------------------------ -------------------- ------------------------------ - ---------
EMP SCOTT PK_DEPT R NO ACTION
EMP P
DEPT P
To see index schema
select TABLE_OWNER, TABLE_NAME, INDEX_NAME from dba_indexes where OWNER='SCOTT'
TABLE_OWNER TABLE_NAME INDEX_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT EMP PK_EMP
SCOTT DEPT PK_DEPT
To see columns used by a specified index
select COLUMN_NAME from dba_ind_columns where INDEX_OWNER='SCOTT' and INDEX_NAME='PK_DEPT';
COLUMN_NAME
--------------------------------------------------------------------------------
DEPTNO
To create index
create index scott.fk_emp__dept on scott.emp(DEPTNO);
Report
note: I have to test this script
select a.owner "Owner",
a.table_name "Table_Name",
a.constraint_name "Constraint_Name",
a.columns "Foreign_Key_Column_1",
b.columns "Foreign_Key_Column_2",
a.owner sdev_link_owner,
a.table_name sdev_link_name,
'TABLE' sdev_link_type
from
( select a.owner, substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
from sys.dba_cons_columns a,
sys.dba_constraints b
where a.constraint_name = b.constraint_name
and a.owner = b.owner
and (:OWNER is null or instr(b.owner, upper(:OWNER))>0)
and b.constraint_type = 'R'
group by a.owner, substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select table_owner,
substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
max(decode(column_position, 1,substr(column_name,1,30),NULL)) ||
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
from sys.dba_ind_columns
group by table_owner, substr(table_name,1,30), substr(index_name,1,30) ) b
where a.owner = b.table_owner (+)
and a.table_name = b.table_name (+)
and substr(a.table_name,1,4) != 'BIN$'
and substr(a.table_name,1,3) != 'DR$'
and b.table_name is null
and b.columns (+) like a.columns || '%'
order by a.owner, a.table_name, a.constraint_name;
