Oracle外键执行计划？

发布于 2025-01-06 04:18:37 字数 883 浏览 1 评论 0原文

考虑以下（简单的）情况：

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.PARENT_ID 进行全表扫描>CHILD 以强制引用完整性）。然而以下语句的执行计划...

DELETE FROM PARENT WHERE PARENT_ID = 1

...不显示表扫描（SYS_C0070229 是 PARENT.PARENT_ID 上的索引）：

query plan

我知道有办法查看所有未索引的外键，但如果我能够“警告”潜在问题，那就更好了查询执行计划本身（顺便说一句，MS SQL Server 和其他数据库可能会这样做）。

在 Oracle 中这可能吗？

如果重要的话，我正在使用 Oracle 10.2。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

等待圉鍢 2025-01-13 04:18:37

我已经更改了您的约束以添加“ON DELETE CASCADE”，否则 Oracle 将引发错误。（外键违规的默认设置是删除限制）

我相信您问题的答案是“否” "，Oracle 不会警告您有关未索引的外键列。实际上，大多数此类列都会建立索引，因为这是将父级连接到子级的方式。

如果您想向某人证明没有索引会导致锁定问题和升级（不太理想的情况），您可以简单地禁用表锁并显示错误。

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,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)'

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

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

回复收藏 0 原文