Estimate the space of index utilized in oracle

Estimate the Space of Index utilized

Estimate the space Index will used
Find how much space a index will need for creating in the database. We can estimate it by package DBMS_SPACE.

DBMS_SPACE.CREATE_INDEX_COST

Calculate the space utilized by dbms_space.create_index_cost procedure
Use the Create statement as first parameter in the procedure , second and third parameter will return the result.
It will return result in bytes.
Following are the example:

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

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.