代码之家 › 专栏 › 技术社区 › pOrinG

建议提高查询性能(Oracle)

oracle11g performance sql

pOrinG · 技术社区 · 6 年前

我有一张116317979条记录的表(A),它每天增长约75万条记录。

根据我的要求,我希望使用日期列(日期时间存储在列中)有效地从表中获取最近3天的完整数据。所以问题是

select * from A where date_column >= trunc(sysdate) - 3

我还需要将表A与表B连接起来,以便

select * from A 
left outer join B 
on A.X = B.X and A.Y = B.Y and A.Z = B.Z and B.M = 'XYZ' and B.N = 'UIM'
where A.date_column >= trunc(sysdate) - 3

表B的唯一索引和主键(X、Y、Z、M、N)

表A(ID)的唯一索引和主键

表A中提议的IDX 1(日期列)

表A(X,Y,Z)中提议的IDX 2

Time without Indexes 34 sec
Time with IDX 1      32 sec
Time with IDX 1 & 2  27 sec //Sorry about the mistype

通过在a.date_列上添加索引,我认为可以显著提高性能,但我的测试结果是阴性的。除了添加新索引之外,是否还有其他可以提高性能的提示?从长远来看,添加这样的索引有什么坏处吗?

或

最好创建另一个表并以某种方式在其中填充最近3天的数据(使用db trigger)。我可以很容易地有另一个过程来清除每晚超过3天的数据。

提前谢谢。

2 回复 | 直到 6 年前

Maxim Borunov 6 年前

oracle分区在这里是有意义的,但即使对于enterprise edition,这也是一个额外的成本选项。如果分区不可用-保持最后3天的单独表应该是性能最佳的。你应该试试。

如果要从索引中获取最大值,则可以考虑使用物理参数:

如果没有更新日期列,并且数据很少被删除,则可以设置 PCTFREE 0
在查看最后一个查询时,我建议在trunc(date)列上创建一个索引,并使用压缩->在这种情况下,每个索引数据块存储更多的条目。在这种情况下,最终的查询条件应该是 trunc(date_column) >= trunc(sysdate) - 3

根据表A中x,y,z的选择性,压缩它们也是有意义的。所以我建议检查两个案例:

create index trunc_date_ai on A(trunc(date_column)) pctfree 0 compress; +你的IDX2
create index trunc_date_ai on A(trunc(date_column),X,Y,Z) pctfree 0 compress; pctfree 0 应在表A中X、Y、Z未更新的情况下使用。 compress 这里的关键字对所有4列进行压缩,因此如果x、y、z值在表A中对于特定的trunc(date_列)是高度可重复的,则值得使用。

要强制使用索引,可以提示查询,例如:

select --+ index (A trunc_date_ai)
       * 
from   A left outer join B 
on A.X = B.X and A.Y = B.Y and A.Z = B.Z and B.M = 'XYZ' and B.N = 'UIM'
where trunc(A.date_column) >= trunc(sysdate) - 3

Gordon Linoff 6 年前

您应该检查执行计划以查看是否正在使用索引。我猜指数 date_column 不使用,32秒和34秒之间的差别只是噪音。

我建议在 A(date_column, X, Y, Z) 对于此查询。

添加索引有什么害处吗?嗯,他们增加了开销 insert S/ update S/ delete 如果你的插入是事务性的,那么你每秒插入大约10行——不包括更新和删除。如果你的峰值明显高于和你的硬件不是很好,那么索引可能会减慢速度。如果额外的行是成批添加的,我就不会担心开销。

我怀疑把这张桌子分成一张单独的3天的桌子会有很大的不同。但为什么要听我说?试试看。取最近3天以上的数据,将其转储到表中,对其进行适当的索引,并查看查询是否更快。