Calculating the size of the index before creating it

posted Jun 2, 2012, 6:17 PM by Sachchida Ojha

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

PL/SQL procedure successfully completed.

SQL> print :used_bytes

USED_BYTES
----------
    25

SQL> print :alloc_bytes;

ALLOC_BYTES
-----------
      65536

SQL> create index cust_idx2 on cust(first_name);

Index created.

SQL> select bytes from user_segments where segment_name='CUST_IDX2';

     BYTES
----------
     65536

SQL>


Comments