Tuesday, September 28, 2010

Un Indexed Foreign Keys

Un indexed foreign keys is meaning a bad schema design. If you do not have index on foreign keys you will probably face situations of
1) deadlocks (http://agstamy.blogspot.com/2010/09/oracle-deadlocks.html)
2) But query plans when joining parent/child table
3) Full table scans causing locks on child tables while updating the parent table pk
4) Problem with cascade delete

There is always an overhead for too many indexes specially on tables that may be high transactional but you will not face the above situations. Bellow are too interesting scripts.

First one you can find the foreign keys referencing a list of parent tables.

select table_name,constraint_name
from dba_constraints
where owner='MY_OWNER'
and r_constraint_name in (
select constraint_name from dba_constraints
where owner='MY_OWNER'
and table_name in ('MY_TABLE','MY_TABLE2',......)
and constraint_type in ('P'));

You can then go and add indexes on the tables FKs columns.

A more automated script by Tomas Kyte.

SELECT
'create index '||owner||'.'||SUBSTR(table_name,INSTR(table_name,'_',1,1)+1)||'_idx'||TO_CHAR(rnk)||' on '||owner||'.'||table_name||'('||col1||');'
FROM (
SELECT
status,owner,table_name,col1,col2
,rnk+1 rnk
FROM(
SELECT status,owner,table_name,col1,col2
,ROW_NUMBER() OVER (PARTITION BY owner ORDER BY owner) rnk
FROM (
SELECT DECODE( b.table_name, NULL, '****', 'ok' ) Status, owner,
a.table_name, a.COLUMNS col1, b.COLUMNS col2
FROM
( SELECT SUBSTR(a.table_name,1,30) table_name, a.owner,
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 dba_cons_columns a, dba_constraints b
WHERE a.owner LIKE 'MY_OWNER'
AND a.owner=b.owner
AND a.constraint_name = b.constraint_name
AND b.constraint_type = 'R'
GROUP BY a.owner,SUBSTR(a.table_name,1,30), SUBSTR(a.constraint_name,1,30) ) a,
( SELECT 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 dba_ind_columns
WHERE index_owner LIKE 'MY_OWNER'
GROUP BY SUBSTR(table_name,1,30), SUBSTR(index_name,1,30) ) b
WHERE a.table_name = b.table_name (+)
AND b.COLUMNS (+) LIKE a.COLUMNS || '%'
) WHERE status !='ok' ORDER BY 2
))
;


Any one can use its own naming conversion for the indexes by altering the first statement and the x on the rnk+x rnk column of the second select.

No comments:

Post a Comment