Aug 262013
 
explain plan for
  create index size_estimation
  on my_humongous_table (
    without_the_pain, of_building_it);
  
select * 
from   table(
  dbms_xplan.display(
    null, null, 'BASIC +NOTE')
);

-----------------------------------------------------
| Id  | Operation              | Name               |
-----------------------------------------------------
|   0 | CREATE INDEX STATEMENT |                    |
|   1 |  INDEX BUILD NON UNIQUE| SIZE_ESTIMATION    |
|   2 |   SORT CREATE INDEX    |                    |
|   3 |    TABLE ACCESS FULL   | MY_HUMONGOUS_TABLE |
-----------------------------------------------------

Note
-----
   - estimated index size: 41M bytes

In addition to helping figure out the size of new indexes, you can also explain plan CREATE TABLE statements, which can be handy to get the plan if this is based on a query.

As with most things, there’s a whole host of caveats surrounding the accuracy of the size estimates. Randolf Geist does a thorough job of discussing these. For a quick and dirty example of the above, keep reading.
Continue reading »