How to Calculate the Initial Size of an Index in Oracle

The Oracle index sorts the data in your tables for faster response in your database applications. Use the Oracle PL-SQL language to calculate the initial size of the index after you create it. The index size lets you examine disk space and determine if your server needs more disk space because of index sizes.

Instructions

1. Open the Oracle Enterprise Manager software and log in to the server. Open the PL-SQL editor to create the index size statement.

2. Type the following code to calculate the index segment size:

   select
   segment_name,
   sum(bytes)/(1024*1024) IndexSize
   from
   user_extents
   where
   segment_type='index'
   and
   segment_name = 'indexname'

   The code above calculates the index size and converts the results to megabytes. Replace "indexname" with the name of the index you want to review. If you want to review each index, remove the "segment_name" filter from the query.

3. Click the "Execute" button to run the statement. The value returned is the total size in megabytes used by the index. As the table data increases, this initial index size increases.

References

• DBA Oracle: Get Oracle Table Size