http://asktom.oracle.com/tkyte/unindex
Unindexed Foreign Keys
Having Unindexed foreign keys can be a performance issue. There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.
To read about this issue, please see the Concepts Guide the section on Maintaining Data Integrity/Concurrency Control, Indexes, and Foreign Keys.
The second issue has to do with performance in general of a parent child relationship. Consider that if you have an on delete cascade and have not indexed the child table (eg: EMP is child of DEPT. Delete deptno = 10 should cascade to EMP. If deptno in emp is not indexed -- full table scan). This full scan is probably undesirable and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.
Also consider that for most (not all, most) parent child relationships, we query the objects from the 'master' table to the 'detail' table. The glaring exception to this is a code table (short code to long description). For master/detail relationships, if you do not index the foreign key, a full scan of the child table will result.
So, how do you easily discover if you have unindexed foreign keys in your schema? This script can help. When you run it, it will generate a report such as:
The **** in the first row shows me that I have an unindexed foreign key in the table APPLICATION_INSTANCES. The ok in the second row shows me I have a table EMP with an indexed foreign key.
Having Unindexed foreign keys can be a performance issue. There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.
To read about this issue, please see the Concepts Guide the section on Maintaining Data Integrity/Concurrency Control, Indexes, and Foreign Keys.
The second issue has to do with performance in general of a parent child relationship. Consider that if you have an on delete cascade and have not indexed the child table (eg: EMP is child of DEPT. Delete deptno = 10 should cascade to EMP. If deptno in emp is not indexed -- full table scan). This full scan is probably undesirable and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted.
Also consider that for most (not all, most) parent child relationships, we query the objects from the 'master' table to the 'detail' table. The glaring exception to this is a code table (short code to long description). For master/detail relationships, if you do not index the foreign key, a full scan of the child table will result.
So, how do you easily discover if you have unindexed foreign keys in your schema? This script can help. When you run it, it will generate a report such as:
SQL> @unindex
STAT TABLE_NAME COLUMNS COLUMNS
---- ------------------------------ -------------------- --------------------
**** APPLICATION_INSTANCES AI_APP_CODE
ok EMP DEPTNO DEPTNO
The **** in the first row shows me that I have an unindexed foreign key in the table APPLICATION_INSTANCES. The ok in the second row shows me I have a table EMP with an indexed foreign key.
posted by Thomas Kyte at 2:48 PM
17 Comments:
The issue I started bloging http://rafudb.blogspot.com/2009/02/unindex.html
Yep, I've learned my lesson with unindexed foreign keys in terms of both table locks and performance.
Hi,
Thanks for this - seems to be a small formatting problem at the bottom of the post though (when viewed in IE 7 anyway)
Regards
Thanks, That was exactly the issue I was having.
HI tom
good morning.i am dilly visitor of your blogs.
i have a querry regarding
Tkprof report?
i have your book,i know how to create a tkprof report.
But unfortuntely i did not understand the trace file which was stored on the UDUMP,Becoz the output
Can you help me regarding this?
Thanks in advance..
@Santu
the tkprof report is what you need - a human readable thing.
If you want the trace file format, I did write that up in Expert one on one Oracle, Wrox Press - but I would encourage you to avoid reading the raw trace file - everything you need is in the tkprof report, nicely formatted!
Hi Tom,
Does the rule of adding an index to a foreign key constrained column also apply in the case where that column is a partition or sub-partition key? In other words, if the column is a partition key, isn't this effectively also an index on that column?
Thanks in advance,
Mark
@mark
we need an *index*, a partition key is not indexed by us, you would have to index it.
it is the index structure that is relevant.
ops$tkyte%ORA11GR2> create table p ( x number primary key );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> CREATE TABLE c
2 (
3 x number references p(x),
4 y varchar2(30)
5 )
6 PARTITION BY RANGE (x)
7 (
8 PARTITION part1 VALUES LESS THAN (2),
9 PARTITION part2 VALUES LESS THAN (3)
10 )
11 /
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into p values ( 1 );
1 row created.
ops$tkyte%ORA11GR2> insert into p values ( 2 );
1 row created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into c values ( 2, 'hello' );
1 row created.
ops$tkyte%ORA11GR2> declare
2 pragma autonomous_transaction;
3 begin
4 delete from p where x = 1;
5 commit;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
ops$tkyte%ORA11GR2> rollback;
Rollback complete.
ops$tkyte%ORA11GR2> create index c_idx on c(x);
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into c values ( 2, 'hello' );
1 row created.
ops$tkyte%ORA11GR2> declare
2 pragma autonomous_transaction;
3 begin
4 delete from p where x = 1;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
when there is no index, self deadlock as the delete tries to lock the child table. When index exists, the delete from parent and insert into child do not conflict.
This is much simpler now with new LISTAGG function in 11g:
SELECT decode(b.table_name, NULL, '****', 'ok') status, a.table_name, a.columns fk_columns, b.columns index_columns
FROM (SELECT a.table_name,
a.constraint_name,
LISTAGG(a.column_name, ',') within GROUP(ORDER BY a.position) columns
FROM dba_cons_columns a,
dba_constraints b
WHERE a.constraint_name = b.constraint_name
AND b.constraint_type = 'R'
AND a.owner = b.owner
GROUP BY a.table_name, a.constraint_name) a,
(SELECT table_name,
index_name,
LISTAGG(c.column_name, ',') within GROUP(ORDER BY c.column_position) columns
FROM dba_ind_columns c
GROUP BY table_name, index_name) b
WHERE a.table_name = b.table_name(+)
AND b.columns(+) LIKE a.columns || '%'
ORDER BY status, table_name
@darko
indeed it is! thanks
@darko
Your listagg version is reportting false negatives. Its listagg ordering is based only on column position. Here you can find another version of unindex query using listagg http://rafudb.blogspot.com/2009/11/unindex-112.html
@Rafu
Not quite sure what do you want to say with "false negatives"!?
Your SQL doesn’t report concatenated keys and indexes created in wrong order of columns.
ExaMPLE:
Parent_table, PK= col1, col2
Child_table, FK=col1,col2 index= col2,col1
How do I get the parent table?
@Saikumar
query user_constraints
Thanks, that was awesome, resolved my issue.
Hi Tom,
I have a parent table A with PK on column C1. This PK is referenced as FK in multiple tables and some of the child tables DO NOT have an index on FK.
Now if I'm NOT updating the PK column on parent table (A) but updating other columns on table A with PK column in the WHERE clause, is there any reason why Oracle should acquire a lock on child table/s or even scan (FULL or INDEX) the child tables?
I ask this because I'm facing this issue in one of our env's (after upgrading from 9.2 to 11.2) and even INSERTS to parent table hanging.
Thanks in advance.
Nik
@Nik,
No, there is not.
do you have a test case for us? should just take one or two rows in each table...
POST A COMMENT
<< Home