Tag Archives: before creating index cost estimate

Estimate the space of index utilized in oracle

Find the Size utilized by index object in Oracle

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