Oracle foreign key execution plan?

Question

Consider the following (simplistic) situation:

CREATE TABLE PARENT (
    PARENT_ID INT PRIMARY KEY
);

CREATE TABLE CHILD (
    CHILD_ID INT PRIMARY KEY,
    PARENT_ID INT NOT NULL,
    FOREIGN KEY (PARENT_ID) REFERENCES PARENT (PARENT_ID)
);

There is no index on CHILD.PARENT_ID, so modifying/deleting PARENT is expensive (Oracle needs to do a full table scan on CHILD to enforce the referential integrity). Yet the execution plan for the following statement...

DELETE FROM PARENT WHERE PARENT_ID = 1

...does not show the table scan (SYS_C0070229 is the index on PARENT.PARENT_ID):

query plan

I know there are ways to see all unindexed FOREIGN KEYs, but it would be even better if I could be "warned" of a potential problem in the query execution plan itself (BTW, MS SQL Server and possibly other databases do that).

Is that possible in Oracle?

I'm using Oracle 10.2 if that matters.

I'm not sure if you have access to this or not, but Oracle SQL Analyze (docs.oracle.com/html/A86647_01/vmqintro.htm) has some powerful features for SQL analysis and tuning. — Zack Macomber
@ZackMacomber Thanks, I'm not familiar with it but I'll make sure I learn. Will it actually do what I asked? — Branko Dimitrijevic

Rajesh Chamarthi Rajesh Chamarthi · Accepted Answer · 2012-02-16T15:52:07

I have altered your constraint to add the "ON DELETE CASCADE", without which Oracle will raise an error.(The default for foreign key violations is delete restrict)

I believe the answer to your question is "NO", Oracle does not warn you about the unindexed foreign key column. In practice, most such columns are indexed, since this is how you would be joining the parent to the child.

If you want to prove to someone that not having an index will cause locking issues and escalations (something not very desirable), you could simply disable the table lock and show the error.

SQL> alter table child disable table lock;

Table altered.

SQL> delete from parent where parent_id = 10;
delete from parent where parent_id = 10
            *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for CHILD

And for the explain plan question, as others have pointed out, the sql to delete from the child table is a recursive SQL and is not shown in the explain plan.

If you TRACE the session, you'll see the recursive SQL.

  1* alter session set SQL_TRACE = TRUE
SQL> /

Session altered.

SQL> delete from parent where parent_id = 10;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter session set SQL_TRACE=FALSe;

Session altered.

=====================
PARSING IN CURSOR #2 len=39 dep=0 uid=65 oct=7 lid=65 tim=763167901560 hv=3048246147 ad='3160891c'
delete from parent where parent_id = 10
END OF STMT
PARSE #2:c=0,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=763167901555
=====================
PARSING IN CURSOR #1 len=48 dep=1 uid=0 oct=7 lid=0 tim=763167976106 hv=2120075951 ad='26722c20'
 delete from "RC"."CHILD" where "PARENT_ID" = :1
END OF STMT
PARSE #1:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=763167976100
EXEC #1:c=0,e=291,p=0,cr=7,cu=7,mis=0,r=2,dep=1,og=4,tim=763168080347
EXEC #2:c=0,e=130968,p=0,cr=8,cu=14,mis=0,r=1,dep=0,og=1,tim=763168091605
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='DELETE  PARENT (cr=8 pr=0 pw=0 time=130887 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=58703 op='INDEX UNIQUE SCAN SYS_C006951 (cr=1 pr=0 pw=0 time=19 us)'
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  CHILD (cr=7 pr=0 pw=0 time=233 us)'
STAT #1 id=2 cnt=2 pid=1 pos=1 obj=58704 op='TABLE ACCESS FULL CHILD (cr=7 pr=0 pw=0 time=76 us)'

Useful Links : http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php

Oracle foreign key execution plan?

2 Answers