ESTIMATING THE SPACE OF INDEX in oracle

DBMS_SPACE.CREATE_INDEX_COST

Estimate how much space it will take via the DBMS_SPACE.CREATE_INDEX_COST procedure

you can calculate the space utilized by dbms_space.create_index_cost procedure.

for example:

SQL> set serveroutput on
SQL> exec dbms_stats.gather_table_stats(user,'CUST');
SQL> variable used_bytes number
SQL> variable alloc_bytes number
SQL> exec dbms_space.create_index_cost( 'create index cust_idx2 on cust(first_name)', :used_bytes, :alloc_bytes );
SQL> print :used_bytes
USED_BYTES
———-
363690SQL> print :alloc_bytes

 

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.