加入时间太长 [英] Join takes too long

问题描述

我有这个查询需要大约5分钟在Oracle中运行：

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有170万行，t2有31万行。我在t2.X和t1.F上有索引。试图在t1.Y，t2.G和t1.H上添加索引，但他们没有帮助。任何想法如何可以提高这个连接的性能？

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?

推荐答案

查看所有相关的字段，涉及可能列

Looking at all the fields involved, a covering index on each table would involve may columns

t1: Y, F, H, A
t2: X, G, C, D, E

如何选择性是任何标准：t2.X，t2.G或t1.Y， t1.F，t1.H？

How selective are ANY of the criteria: t2.X, t2.G or t1.Y, t1.F, t1.H ?

如果没有一个列具有足够的选择性（理想情况下为0.5％或更小），则可能需要创建一个或多个覆盖索引例如

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

t2 (G, X)
t1 (H, F, Y)

注意：索引中列的顺序非常重要 - 总是把最有选择性的（将列数据分成最不相同的集合的）。

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.

create index ix_t2 on t2 (G,X) INCLUDE (C,D,E)
create index ix_t1 on t1 (H,F,Y) INCLUDE (A)

EDIT

看起来我在那里滑了一个SQL Server DDL。在Oracle中，您将不得不将索引扩展为t2（G，X，C，D，E），但是这会增加索引使用的选择性要求，因为索引键变得非常长。

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.

这篇关于加入时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！