Estimating index creation time in oracle

Question

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)?

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

Answer 1

Oracle can estimate index creation time and index size with the EXPLAIN PLAN command:

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.

Answer 2

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.