Close Menu
Performance

DBMS_SPACE.CREATE_INDEX_COST – How to Estimate Index Size Before Creating It

HenriqueBy 7 Mins Read

This post is also available in: Português (Portuguese (Brazil))

Before creating an index on a production table with millions of rows, the inevitable question is: how much space will it consume?

Creating an index without this estimate can cause serious problems — tablespace full during creation, unexpected storage impact, or simply a surprise on the cloud bill. The good news is that Oracle provides a native way to estimate this without actually creating the index.

In this article, I’ll show how to use DBMS_SPACE.CREATE_INDEX_COST to estimate index sizes before creating them, with practical examples, real validation, and limitations you need to know.

What CREATE_INDEX_COST Does

The DBMS_SPACE.CREATE_INDEX_COST procedure takes a CREATE INDEX statement as input and returns two estimates:

VariableWhat it represents
usedSpace needed for the index data (bytes) — the useful size
allocTotal space that will be allocated in the tablespace (bytes) — includes block overhead, extents, and metadata

The difference between the two is normal — Oracle allocates space in extents, so alloc is always larger than used.

Prerequisites

For accurate estimates:

  • The table where the index will be created must exist
  • Statistics on the table must be up to date (DBMS_STATS)
  • The more current the statistics, the better the estimate

Basic Example — Simple Index

Step 1 — Prepare the variables

SET SERVEROUTPUT ON
VARIABLE used_bytes NUMBER
VARIABLE alloc_bytes NUMBER

Step 2 — Estimate the size

EXEC DBMS_SPACE.CREATE_INDEX_COST( -
  'CREATE INDEX idx_orders_date ON orders(order_date)', -
  :used_bytes, -
  :alloc_bytes -
);

Step 3 — View the result in MB

SELECT
  ROUND(:used_bytes / 1024 / 1024, 2) AS used_mb,
  ROUND(:alloc_bytes / 1024 / 1024, 2) AS alloc_mb
FROM dual;

Result (example):

   USED_MB   ALLOC_MB
---------- ----------
     55.00     264.00

The estimate indicates the index will use ~55 MB of actual data and Oracle will allocate ~264 MB in the tablespace.

Composite Index Example (Multiple Columns)

EXEC DBMS_SPACE.CREATE_INDEX_COST( -
  'CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date)', -
  :used_bytes, -
  :alloc_bytes -
);

SELECT
  ROUND(:used_bytes / 1024 / 1024, 2) AS used_mb,
  ROUND(:alloc_bytes / 1024 / 1024, 2) AS alloc_mb
FROM dual;

Composite indexes are larger because each index entry stores values from all key columns.

Example with Specific Tablespace

If you want to estimate considering a tablespace with different extent configuration:

EXEC DBMS_SPACE.CREATE_INDEX_COST( -
  'CREATE INDEX idx_orders_date ON orders(order_date) TABLESPACE idx_ts', -
  :used_bytes, -
  :alloc_bytes -
);

Oracle considers the extent size of the idx_ts tablespace when calculating alloc.

Full Example — Create, Estimate, and Validate

Let’s build a scenario from scratch to demonstrate the estimate’s accuracy.

Create a test table

CREATE TABLE teste AS SELECT * FROM all_objects;

-- Insert more data to simulate volume
INSERT INTO teste (SELECT * FROM teste);
-- Repeat until you have significant volume (e.g., ~5 million rows)

COMMIT;

SELECT COUNT(*) FROM teste;

Gather statistics

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'TESTE',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE
  );
END;
/

Estimate the index size

VARIABLE used_bytes NUMBER
VARIABLE alloc_bytes NUMBER

EXEC DBMS_SPACE.CREATE_INDEX_COST( -
  'CREATE INDEX idx_teste_objid ON teste(object_id)', -
  :used_bytes, -
  :alloc_bytes -
);

SELECT
  ROUND(:used_bytes / 1024 / 1024, 2) AS estimated_used_mb,
  ROUND(:alloc_bytes / 1024 / 1024, 2) AS estimated_alloc_mb
FROM dual;

Create the index and compare

CREATE INDEX idx_teste_objid ON teste(object_id);

SELECT
  segment_name,
  ROUND(bytes / 1024 / 1024, 2) AS actual_mb
FROM dba_segments
WHERE segment_name = 'IDX_TESTE_OBJID';

Compare estimate vs actual

Estimated alloc:   264.00 MB
Actual size:       262.44 MB
Difference:        ~0.6%

The estimate is usually very close to the actual value when statistics are current.

Estimating Table Sizes with CREATE_TABLE_COST

The same DBMS_SPACE package offers CREATE_TABLE_COST for estimating table sizes. Useful for capacity planning:

VARIABLE used_bytes NUMBER
VARIABLE alloc_bytes NUMBER

EXEC DBMS_SPACE.CREATE_TABLE_COST( -
  'USERS',          -- tablespace_name
  8000,             -- avg_row_size (bytes)
  1000000,          -- row_count
  0,                -- pct_free
  :used_bytes,      --
  :alloc_bytes      --
);

SELECT
  ROUND(:used_bytes / 1024 / 1024, 2) AS used_mb,
  ROUND(:alloc_bytes / 1024 / 1024, 2) AS alloc_mb
FROM dual;

Limitations and Considerations

Before blindly trusting the estimate, be aware of the limitations:

LimitationImpact
Stale statisticsIf table statistics are old or missing, the estimate will be inaccurate
Bitmap indexesCREATE_INDEX_COST does not work for bitmap index estimation
Domain indexes (Oracle Text, Spatial)Not supported
Function-based indexesWorks, but requires statistics on the expression to be gathered
Partitioned tablesThe estimate is for the entire table, not per individual partition
Index compressionIf you’ll use COMPRESS, actual size will be smaller than estimated
LOB columnsNot applicable — LOBs cannot be directly indexed

Golden rule

Always gather fresh statistics before running CREATE_INDEX_COST. Stale statistics = wrong estimate.

-- Gather before estimating
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TABLE_NAME');

Reusable Script

Save this script for whenever you need to estimate indexes:

-- estimate_index.sql
-- Usage: @estimate_index.sql
-- Modify the CREATE INDEX below for your scenario

SET SERVEROUTPUT ON
VARIABLE used_bytes NUMBER
VARIABLE alloc_bytes NUMBER

EXEC DBMS_SPACE.CREATE_INDEX_COST( -
  'CREATE INDEX idx_name ON schema.table(col1, col2)', -
  :used_bytes, -
  :alloc_bytes -
);

SELECT
  'Estimate' AS type,
  ROUND(:used_bytes / 1024 / 1024, 2) AS used_mb,
  ROUND(:alloc_bytes / 1024 / 1024, 2) AS alloc_mb,
  ROUND(:alloc_bytes / 1024 / 1024 / 1024, 2) AS alloc_gb
FROM dual;

Conclusion

DBMS_SPACE.CREATE_INDEX_COST is an essential tool for any DBA who needs to plan before acting. In production environments with tables containing hundreds of millions of rows, creating an index without estimating space is an unnecessary risk.

The recommended workflow before creating any index in production:

  1. Gather fresh statistics on the table
  2. Estimate the size with CREATE_INDEX_COST
  3. Verify the tablespace has enough space
  4. Create the index with confidence

Simple, fast, and prevents surprises.

References:

Share.

Related Posts

Add A Comment
Leave A Reply