Estimate Index Size

Many times when we have to calculate an index size, or should calculate the space we need because we could having to extend the tablespace, of course, you can always run the create index command and if it fails to add more datafiles and reexecute. In this post we will remember that Oracle has a tool to help with this issue, using the DBMS_SPACE  package.

We just pass the create command “CREATE INDEX ON TAB1_INX TAB1 (COL1)”

 
Run from sqlplus

 

SET SERVEROUTPUT ON SIZE UNLIMITED
VARIABLE A_Bytes NUMBER
VARIABLE E_Bytes NUMBER
exec dbms_space.create_index_cost( ‘CREATE INDEX TAB1_INX ON TAB1 (COL1)’, –
:A_Bytes, :E_Bytes );
print E_Bytes

 

Very important (From documentation)

  • The table on which the index is created must already exist.
  • The computation of the index size depends on statistics gathered on the segment.
  • It is imperative that the table must have been analyzed recently.
  • In the absence of correct statistics, the results may be inaccurate, although the procedure will not raise any errors.

 

HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s