加入时间太长
我有一个查询，在 Oracle 中运行大约需要 5 分钟：
select t1.A,t2.B,t2.C,t2.D,t2.E
from TABLE1 t1 join TABLE2 t2
on t2.X = t1.Y
where t1.F = <integer>
and t2.G = <integer>
and t1.H = <integer>
t1 有 170 万行，t2 有 3100 万行。我有 t2.X 和 t1.F 的索引。尝试在 t1.Y、t2.G 和 t1.H 上添加索引，但没有帮助。有什么想法可以提高此连接的性能吗？
I have this query that takes about 5 minutes to run in Oracle:
select t1.A,t2.B,t2.C,t2.D,t2.E
from TABLE1 t1 join TABLE2 t2
on t2.X = t1.Y
where t1.F = <integer>
and t2.G = <integer>
and t1.H = <integer>
t1 has 1.7 million rows and t2 has 31 million. I have indices on t2.X and t1.F. Tried adding indices on t1.Y, t2.G and t1.H but they didn't help. Any ideas how I can improve performance of this join?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
查看涉及的所有字段，每个表上的覆盖索引将涉及可能的列
任何条件的选择性如何：t2.X，t2.G或t1.Y，t1.F，t1.H？
如果没有一个单独的列具有足够的选择性（理想情况下为 0.5% 或更少），您可能需要创建一个或多个涉及多个列的覆盖索引，例如
注意： 列的顺序在索引中非常重要 - 始终将最具选择性的（将列数据划分为最不同的集合的索引）放在第一位。
以存储为代价，您可以通过提供所有索引来使索引覆盖查询索引本身中的必要列。这意味着查询根本不需要返回表数据。
编辑
看起来我在其中插入了 SQL Server DDL。在 Oracle 中，您必须将索引扩展为 t2 (G,X, C,D,E) - 但这会增加索引使用的选择性要求，因为索引键变得非常长。
Looking at all the fields involved, a covering index on each table would involve may columns
How selective are ANY of the criteria: t2.X, t2.G or t1.Y, t1.F, t1.H ?
If none of the individual columns are selective enough (ideally 0.5% or less), you may want to create a or several covering index that involve multiple columns, for example
Note: The order of columns in an index is very important - always put the most selective (the one that divides the column data into the most distinct sets) first.
At the expense of storage, you can make the index COVER the query by providing all the necessary columns in the index itself. This means the query does not need to go back to table data at all.
EDIT
Looks like I slipped a SQL Server DDL in there. In Oracle, you would have to expand the index to become t2 (G,X, C,D,E) - but that increases selectivity requirements for index usage since the index key becomes very long.