0028680: Create a index for each column used on a foreign key constraint

Description

Create a index for each column used on a foreign key constraint.

Steps To Reproduce

Check that not all the columns used on a foreign key constraint have an index:

Execute on oracle:

SELECT
p.table_name parent_table, c.table_name || '.' || cc.column_name referenced_by
FROM user_constraints c, user_cons_columns cc, user_constraints p
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
AND c.r_constraint_name = p.constraint_name
AND p.constraint_type = 'P'
and not exists (
SELECT i.table_name, ic.column_name, ic.column_position
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name
and i.table_name = c.table_name
and ic.column_name = cc.column_name
and ic.column_position = cc.position
)
ORDER BY cc.table_name, cc.column_name
;

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0073620) egoitz (viewer) 2015-01-19 17:45	http://www.dba-oracle.com/t_find_foreign_keys_with_no_index.htm [^] http://www.dba-oracle.com/t_foreign_key_indexing.htm [^]

Issue History
Date Modified	Username	Field	Change
2015-01-19 17:45	egoitz	New Issue
2015-01-19 17:45	egoitz	Assigned To	=> AugustoMauch
2015-01-19 17:45	egoitz	OBNetwork customer	=> Yes
2015-01-19 17:45	egoitz	Modules	=> Core
2015-01-19 17:45	egoitz	Triggers an Emergency Pack	=> No
2015-01-19 17:45	egoitz	Note Added: 0073620