Oracle外键执行打算？

思量以下(简朴)环境：

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)
);

CHILD.PARENT_ID上没有索引,因此修改/删除PARENT很是昂贵(Oracle必要对CHILD执行全表扫描以逼迫执行参照完备性).然而,以下声明的执行打算……

DELETE FROM PARENT WHERE PARENT_ID = 1

…不表现表扫描(SYS_C0070229是PARENT.PARENT_ID上的索引)：

我知道see all unindexed FOREIGN KEYs有步伐,但假如我可以“告诫”查询执行打算自己的隐藏题目(BTW,MS SQL Server和也许的其他数据库那样做)会更好.

这也许在Oracle中吗？

假如重要的话,我正在行使Oracle 10.2.

办理要领

我信托你的题目的谜底是“不”,甲骨文没有告诫你关于无索引的外键列.现实上,大大都此类列都已体例索引,由于这是您将父级插手子级的方法.

假如你想向没有索引的人证明会导致锁定题目和进级(不太抱负),你可以简朴地禁用表锁并表现错误.

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

对付表明打算题目,正如其他人所指出的那样,从子表中删除的sql是一个递归SQL,并没有在表明打算中表现.

假如您跟踪会话,您将看到递归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,dep=1,og=4,tim=763167976100
EXEC #1:c=0,e=291,cr=7,cu=7,r=2,tim=763168080347
EXEC #2:c=0,e=130968,cr=8,cu=14,r=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)'

有效链接：http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!