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.

create table my_humongous_table (
  without_the_pain integer,
  of_building_it   varchar2(20)
);

begin
  dbms_stats.gather_table_stats(
    user, 'my_humongous_table');
end;
/

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')
);

-- stick a load of data in, 
-- and we'll see the size estimate increases
insert into my_humongous_table
  select rownum, dbms_random.string('x', 20)
  from   dual 
  connect by level <= 1000000;
  
commit;

begin
  dbms_stats.gather_table_stats(
    user, 'my_humongous_table');
end;
/

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')
);

-- if we include all the explain info, 
-- we also get an esimate of how long 
-- creation will take!
select * 
from   table(
  dbms_xplan.display(
    null, null, 'ALL')
);
  
drop table my_humongous_table purge;

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>