在oracle中估计索引创建时间 [英] Estimating index creation time in oracle

问题描述

我在Oracle环境中有一些表，这些表可以从新索引中受益.但是，它们是很大的表，范围从1M寄存器到300M寄存器，因此我首先尝试估计进行索引创建将花费多少时间，因此我至少知道它将花费的数量级(小时)，天，周)?

I have some tables in Oracle enviroment which I have found could benefit from new indexes. However, they are big tables, ranging from 1M registers to 300M registers, so I would first try to estimate how much time it would take for the index creation take place, so I would know at least the order of magnitude it would take (hours, days, weeks)?

是否有一些启发式/oracle函数/经验法则可以帮助我解决此问题?

Is there some heuristics/oracle function/rule of thumb that could help me into solving this issue?

推荐答案

确实有太多因素需要考虑，例如机器速度，内存等会影响创建时间的因素.另外，数据本身的性质可能会对创建时间产生重大影响.

There are really too many factors to consider, such as machine speed, memory, etc. that could impact the creation time. Plus, the nature of the data itself could have a significant effect on the creation time.

我要做的是选择一个较大的表，在其上创建一个索引，然后查看需要花费多长时间.然后，花费时间并除以表中的行数，这应该为您提供一个大致的指标.再次注意，这并不精确，但这只是您可以使用的经验法则.它将有很大的不同，因为某些表具有更多的列，较少的稀疏列值，等等，但这是一个起点.

What I would do is pick one of the larger tables, create an index on it and see how long it takes. Then, take the time it took and divide by the number of rows in the table and that should give you a rough metric for what to expect. Note again, this is not going to be precise, but it's just a rule of thumb you could use. It's going to vary a lot because some tables have more columns, less sparse column values, etc., but it's a starting point.

Ex.  It takes 3600 seconds to create a index on table X, which has 3 million rows.
So the metric is 3600 / 3,000,000 = 0.0012 seconds per row.

So if table Y has 8 million rows, you could expect
.0012 * 8,000,000 = 9600 seconds (or 160 minutes) to create the index.

这篇关于在oracle中估计索引创建时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！