Estimate Index size before creating

      Sometimes it is better to estimate the index before creating so to not see any surprise errors. From the below Oracle note got the exact code to get estimated size of index.

How to Estimate the Size of Tables and Indexes Before Being Created and Populated in the Database? (Doc ID 1585326.1)

I have queried this in 11204 database, please do comment if you test in other versions.

SQL> set serveroutput on 
SQL> declare
  2  l_used_bytes number;
  3  l_alloc_bytes number;
  4  begin
  5  dbms_space.create_index_cost (
  6  ddl => 'create index orp.ACT_IDX on orp.act (LOAN) global partition by hash (LOAN) partitions 32',
  7  used_bytes => l_used_bytes,
  8  alloc_bytes => l_alloc_bytes
  9  );
 10  dbms_output.put_line ('Used Bytes          = '||l_used_bytes);
 11  dbms_output.put_line ('Allocated Bytes     = '||l_alloc_bytes);
 12  end;
 13  /
Used Bytes              = 3464634958
Allocated Bytes         = 42949672960

PL/SQL procedure successfully completed.

 The used_bytes : how much space is for the index data

 The alloc_bytes : how much space is allocated within the tablespace for the index segment. (ACTUAL SIZE)

Another Way

There is another way using explain plan, please don't confuse with the numbers both are different indexes just trimmed names for easy

 SQL> explain plan for 

   2  create index orp.ACT_IDX on orp.act (PRO_TIME);

Explained.

01:42:48 SQL> select * from table (dbms_xplan.display());

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------

Plan hash value: 3821618068

-------------------------------------------------------------------------------------------------------

| Id  | Operation              | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------

|   0 | CREATE INDEX STATEMENT |                              | 46441 |  1224K|   113   (1)| 00:00:02 |

|   1 |  INDEX BUILD NON UNIQUE| ACT_IDX                      |       |       |            |          |

|   2 |   SORT CREATE INDEX    |                              | 46441 |  1224K|            |          |

|   3 |    INDEX FAST FULL SCAN| ACT_IDX                      | 46441 |  1224K|    71   (2)| 00:00:01 |

-------------------------------------------------------------------------------------------------------

Note

-----

   - estimated index size: 2097K bytes

14 rows selected.