连接查询中未选择外键索引
考虑 EMP、DEPT 示例 在 deptno 上下文中查询 EMP 表是很常见的。如果你经常 查询：
select * from dept, emp
where emp.deptno = dept.deptno and dept.deptno = :X;
我在员工表上有一个索引 deptNo_idx 。但每当我看到执行计划时，这个索引就不会被使用。即使给出索引提示也没有用，
知道如何在外键列上使用索引吗？
Consider the EMP, DEPT example
It is very common to query the EMP table in the context of a deptno. If you frequently
query:
select * from dept, emp
where emp.deptno = dept.deptno and dept.deptno = :X;
i have a Index deptNo_idx on employee table . but whenever i see the plan of execution this index is never used. even giving an index hint was not useful
any idea how to use the index on the foreign key column?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
当您尝试了解优化器行为时，
EMP和DEPT表并不是特别适合使用的表。它们是如此之小，以至于优化器经常会正确地确定全表扫描比索引访问更有效。此外，不同的DEPTNO值相对较少，因此优化器知道它必须从EMP表中获取相对较大百分比的行。由于表中的行很少，并且您需要为任何给定的 DEPTNO 值获取大部分行，因此表扫描会更有效。如果表更大且部门更多，优化器更有可能确定使用索引会更有效。
The
EMPandDEPTtables are not particularly good tables to use when you're trying to understand optimizer behavior. They're so small that the optimizer will frequently correctly determine that full table scans are more efficient than index access. Additionally, there are relatively few distinctDEPTNOvalues so the optimizer knows that it's going to have to fetch a relatively large percentage of the rows from theEMPtable. Since there are few rows in the tables and you need to fetch a large fraction of the rows for any givenDEPTNOvalue, a table scan will be more efficient.If the tables were larger and there were more departments, the optimizer would be more likely to determine that using the index would be more efficient.
A) 更改语法以使用正确的连接：
B) 在 dept.deptno 上定义一个索引，因为这是主要过滤器（where 子句），
这应该使它能够运行。
A) Change the syntax to use a proper join:
B) Define an index on dept.deptno, since that's the primary filter (the where clause)
That should make it fly.