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