外键约束会影响 Oracle 中的查询转换吗？
我遇到这样的情况：
create table a(
a_id number(38) not null,
constraint pk_a primary key (id)
);
create table b(
a_id number(38) not null
);
create index b_a_id_index on b(a_id);
现在 b.a_id 实际上是引用 a.a_id 的外键，但它并未正式声明为这样。显然，这应该是出于诚信的原因。但是，外键约束在一般情况下还是在特定情况下是否也会提高连接性能？如果是，用于什么类型的查询转换？
有关于这个主题的相关文档吗？
我使用的是 Oracle 11g (11.2.0.2.0)
I have a situation like this:
create table a(
a_id number(38) not null,
constraint pk_a primary key (id)
);
create table b(
a_id number(38) not null
);
create index b_a_id_index on b(a_id);
Now b.a_id is in fact meant to be a foreign key referencing a.a_id, but it isn't formally declared as such. Obviously, it should be for integrity reasons. But does a foreign key constraint also improve join performance in general or in specific cases? If yes, for what types of query transformations?
Is there any relevant documentation about this topic?
I'm using Oracle 11g (11.2.0.2.0)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
是的，设置外键约束可以提高查询性能。当存在通常不可用的适当外键约束时，优化器可以使用各种转换。例如，如果您要连接
A和B但仅从B选择数据，优化器可能会消除A> 如果存在外键约束，则完全来自查询计划（当您拥有有用的视图，这些视图加入的表数量多于当前查询严格需要的表时，这种事情会非常方便，因为您不必进行交易额外连接的性能成本与使用现有视图的代码重用相比）。当您执行诸如使用查询重写之类的操作来重写查询以在数据仓库/DSS 类型系统中使用物化视图之类的操作时，它们也会派上用场。Tom Kyte 做了演讲 元数据很重要< /a> 讨论了各种类型如何约束以及其他元数据都会影响优化器。
Yes, having foreign key constraints in place can improve query performance. There are various transforms that are open to the optimizer when appropriate foreign key constraints exist that are not generally available. For example, if you were to join
AandBbut only select data fromB, the optimizer could eliminateAfrom the query plan entirely if there was a foreign key constraint in place (this sort of thing comes in very handy when you've got useful views that join in more tables than your current query strictly needs because you don't have to trade the performance costs of the extra joins against the code reuse from using an existing view). They also come in handy when you're doing things like using things like query rewrite to rewrite a query to use a materialized view in a data warehouse/ DSS type system.Tom Kyte has a presentation Metadata Matters that talks about how various types of constraints, along with other pieces of metadata, can influence the optimizer.
正如 Justin 已经指出的，JOIN 消除是一种基本的非基于成本的 SQL 转换，可以根据存在来应用仅元数据。我最近在博客上谈到了这一点：
正如我最初假设的那样，有很多 SQL 转换依赖于元数据，因此添加外键约束（和其他约束）肯定会影响性能积极的方式。
As Justin already pointed out, JOIN elimination is an essential non-cost based SQL transformation, which can be applied based on the presence of meta data only. I have blogged about this more recently:
As I originally assumed, there are a lot of SQL transformations that depend on meta data, so adding foreign key constraints (and other constraints) definitely can impact performance in a positive way.