oracle 在oracle中估算索引创建时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3071930/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Estimating index creation time in oracle
提问by kurast
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 环境中有一些表可以从新索引中受益。然而，它们是大表，范围从 1M 寄存器到 300M 寄存器，所以我会首先尝试估计创建索引需要多长时间，所以我至少知道它需要的数量级（小时、天、周）？
Is there some heuristics/oracle function/rule of thumb that could help me into solving this issue?
是否有一些启发式/oracle 功能/经验法则可以帮助我解决这个问题？
采纳答案by dcp
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.
回答by Jon Heller
Oracle can estimate index creation time and index size with the EXPLAIN PLANcommand:
Oracle 可以使用以下EXPLAIN PLAN命令估算索引创建时间和索引大小：
Sample Schema
示例架构
--Create a table with 1 million rows.
drop table table1;
create table table1(a number);
insert into table1 select level from dual connect by level <= 1000000;
--Gather statistics.
begin
dbms_stats.gather_table_stats(user, 'table1');
end;
/
--Estimate index creation and size.
explain plan for create index table1_idx on table1(a);
select * from table(dbms_xplan.display);
Results
结果
Plan hash value: 290895522
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 1000K| 4882K| 683 (2)| 00:00:10 |
| 1 | INDEX BUILD NON UNIQUE| TABLE1_IDX | | | | |
| 2 | SORT CREATE INDEX | | 1000K| 4882K| | |
| 3 | TABLE ACCESS FULL | TABLE1 | 1000K| 4882K| 254 (5)| 00:00:04 |
-------------------------------------------------------------------------------------
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
- estimated index size: 24M bytes
Notes
笔记
The actual creation time on my system was 2.5 seconds, compared with the estimate of 10 seconds. But that's still good enough if you're only looking for an order of magnitude estimate. The accuracy depends on having accurate table statistics as well as good system statistics. (But be careful before you gather system statistics, it may influence a lot of execution plans!) You can further fiddle with the settings by manually modifying sys.aux_stats$. That's one of the few SYS tables that it's OK to modify, although you still need to be careful.
我系统上的实际创建时间为 2.5 秒，而估计为 10 秒。但如果你只是在寻找一个数量级的估计，这仍然足够好。准确性取决于具有准确的表统计信息以及良好的系统统计信息。（但是在收集系统统计信息之前要小心，它可能会影响很多执行计划！）您可以通过手动修改sys.aux_stats$. 这是少数可以修改的 SYS 表之一，尽管您仍然需要小心。
