Oracle Index Size Estimator

Purpose

Estimates the storage footprint of a proposed B-tree index before creating it. Useful during index design to evaluate the storage cost against available tablespace before committing — particularly on large tables or Exadata environments where storage is allocated in fixed increments.

The script uses USER_TAB_COLUMNS and USER_TAB_COL_STATISTICS to determine each proposed key column’s average byte length from gathered statistics, rather than just the column definition maximum. This makes the estimate more accurate for variable-length columns such as VARCHAR2 and NVARCHAR2 where actual data is typically much shorter than the declared maximum.

The estimate applies Oracle’s B-tree leaf-entry structure: each entry carries a header (2 bytes), a ROWID (10 bytes for standard heap tables), and the column length-prefix bytes alongside each column value. Total index size is projected using the database block size (typically 8192 bytes) and PCTFREE, which is Oracle’s equivalent of fill factor.

Supported Platforms and Versions

Oracle Database 19c, 21c, and 23ai (on-premises and Oracle Cloud Infrastructure). The script uses standard USER_* catalog views available across all Oracle Database editions.

Required Permissions

SELECT on USER_TAB_COLUMNS, USER_TABLES, and USER_TAB_COL_STATISTICS — available to the table owner by default. If the table belongs to another schema, use the corresponding ALL_* or DBA_* views and add the schema name to each reference.

Preconditions

• The target table must already exist.
• Statistics must be gathered for accurate average column length data. Run DBMS_STATS.GATHER_TABLE_STATS if statistics are stale or missing — a NULL in AVG_COL_LEN will cause the estimate to fall back to a fixed width.
• USER_TABLES.NUM_ROWS is used for the row count estimate. This value is updated by DBMS_STATS.GATHER_TABLE_STATS. Run statistics if the table has had significant DML since the last gather. Alternatively, replace t.num_rows in the row_estimate CTE with a live count — for example (SELECT COUNT(order_id) FROM orders) using the primary key column — for an exact figure when statistics are stale. This executes a full table scan and may be slow on large tables.
• DB_BLOCK_SIZE is typically 8192 bytes but may differ. Confirm with SHOW PARAMETER DB_BLOCK_SIZE or query V$PARAMETER.

Risk Level

Low. Read-only. No index is created. No changes to schema or data.

Script

-- ============================================================
-- Oracle Index Size Estimator
-- Permissions : SELECT on USER_TAB_COLUMNS, USER_TABLES,
--               USER_TAB_COL_STATISTICS (or ALL_/DBA_ variants)
-- Risk        : Read-only — estimates only, no index is created
-- ============================================================

WITH params AS (
    SELECT
        8192        AS db_block_size,    -- confirm with: SHOW PARAMETER DB_BLOCK_SIZE
        10          AS pctfree,          -- Oracle PCTFREE (space reserved in each block for updates)
        'ORDERS'    AS table_name        -- target table name (upper-case in Oracle catalog)
    FROM dual
),
-- Define the proposed index columns.
-- Oracle B-tree does not distinguish key vs INCLUDE columns — all listed
-- columns appear as key entries. Adjust to match your CREATE INDEX column list.
index_columns AS (
    SELECT column_name FROM (
        SELECT 'ORDER_DATE'   AS column_name, 1 AS col_order FROM dual
        UNION ALL SELECT 'CUSTOMER_ID',  2 FROM dual
        UNION ALL SELECT 'STATUS',       3 FROM dual
    )
    ORDER BY col_order
),
col_avg_lengths AS (
    SELECT
        ic.column_name,
        -- Use gathered avg_col_len from statistics if available;
        -- fall back to column data_length maximum if statistics are missing.
        COALESCE(cs.avg_col_len, tc.data_length, 8)     AS avg_bytes
    FROM index_columns     AS ic
    JOIN params            AS p  ON 1 = 1
    JOIN user_tab_columns  AS tc ON tc.table_name  = p.table_name
                                AND tc.column_name = ic.column_name
    LEFT JOIN user_tab_col_statistics AS cs
                           ON cs.table_name  = p.table_name
                          AND cs.column_name = ic.column_name
),
entry_size AS (
    SELECT
        -- Oracle B-tree leaf entry structure per column:
        --   1 byte  length prefix per column (2 bytes for columns > 250 bytes)
        --   N bytes column value (avg_bytes)
        -- Entry overhead:
        --   2 bytes entry header
        --  10 bytes ROWID (standard heap table)
        SUM(avg_bytes + 1)  AS col_data_bytes,
        SUM(avg_bytes + 1) + 2 + 10  AS leaf_entry_bytes
    FROM col_avg_lengths
),
row_estimate AS (
    SELECT
        t.num_rows                                              AS row_count,
        e.leaf_entry_bytes,
        p.db_block_size,
        p.pctfree,
        p.table_name,
        -- Usable bytes per block = block size minus block header (90 bytes typical)
        -- further reduced by PCTFREE reservation.
        FLOOR((p.db_block_size - 90) * (1 - p.pctfree / 100))  AS usable_bytes_per_block
    FROM entry_size     AS e
    CROSS JOIN params   AS p
    JOIN user_tables    AS t ON t.table_name = p.table_name
)
SELECT
    table_name,
    row_count,
    leaf_entry_bytes                                                AS estimated_entry_bytes,
    CEIL(row_count / FLOOR(usable_bytes_per_block / leaf_entry_bytes))
                                                                    AS estimated_leaf_blocks,
    ROUND(
        CEIL(row_count / FLOOR(usable_bytes_per_block / leaf_entry_bytes))
        * db_block_size / 1024 / 1024,
    2)                                                              AS estimated_leaf_mb,
    -- Add ~10% for branch and root B-tree levels above the leaf layer
    ROUND(
        CEIL(row_count / FLOOR(usable_bytes_per_block / leaf_entry_bytes))
        * db_block_size / 1024 / 1024 * 1.10,
    2)                                                              AS estimated_total_mb
FROM row_estimate;

Safe Execution Guidance

1. Edit the params CTE: set table_name (upper-case, as Oracle stores object names in upper-case by default), db_block_size, and pctfree.
2. Edit the index_columns subquery to list the proposed index key columns in order.
3. Confirm statistics are current before running. If USER_TAB_COL_STATISTICS returns no rows for the target table, run:

   EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');

4. If the table belongs to another schema, replace USER_TAB_COLUMNS, USER_TABLES, and USER_TAB_COL_STATISTICS with ALL_TAB_COLUMNS, ALL_TABLES, and ALL_TAB_COL_STATISTICS, and add a OWNER = '<schema>' filter in each join.
5. Run in SQL*Plus, SQL Developer, or any Oracle client. No special session settings are required.

Expected Output

Single row:

Comparison with DBMS_SPACE.CREATE_INDEX_COST

Oracle ships a built-in alternative: DBMS_SPACE.CREATE_INDEX_COST. It accepts a full CREATE INDEX DDL string and returns two output parameters — used_bytes (bytes consumed by index data) and alloc_bytes (bytes allocated including free space due to PCTFREE).

DECLARE
  v_used_bytes  NUMBER;
  v_alloc_bytes NUMBER;
BEGIN
  DBMS_SPACE.CREATE_INDEX_COST(
    ddl         => 'CREATE INDEX idx_orders ON orders (order_date, customer_id, status)',
    used_bytes  => v_used_bytes,
    alloc_bytes => v_alloc_bytes
  );
  DBMS_OUTPUT.PUT_LINE('Used  : ' || ROUND(v_used_bytes  / 1024 / 1024, 2) || ' MB');
  DBMS_OUTPUT.PUT_LINE('Alloc : ' || ROUND(v_alloc_bytes / 1024 / 1024, 2) || ' MB');
END;
/

Use DBMS_SPACE.CREATE_INDEX_COST when you have the exact DDL ready and want a fast Oracle-authoritative result — it handles compression and IOT structures automatically. Use this script when you want to inspect per-column size contribution, compare column combinations without writing DDL for each, or when EXECUTE on DBMS_SPACE is not granted.

Limitations

• Statistics currency: The estimate relies on AVG_COL_LEN from USER_TAB_COL_STATISTICS. If statistics are missing or stale, the script falls back to the column’s DATA_LENGTH maximum, which will produce a pessimistic (high) estimate for variable-length columns.
• ROWID size: This estimate assumes a standard 10-byte ROWID for heap-organised tables. Index-Organised Tables (IOTs) use a different entry structure and this estimate does not apply to them.
• Block header size: The 90-byte block header deduction is a typical value. Actual overhead depends on Oracle version and block configuration. For precise values, query V$PARAMETER for db_block_size and reference the Oracle Concepts guide for your version.
• Branch levels: The 10% overhead added for estimated_total_mb covers typical B-tree branch and root block overhead for large leaf layers. Extremely wide indexes with many key columns may have proportionally more branch-level overhead.
• Compression: If COMPRESS or Advanced Index Compression is enabled for the proposed index, actual storage will be smaller than this estimate. Oracle’s compression applies a prefix compression scheme to repeated leading key values.

Rollback Steps

Not applicable — read-only script.

Official References

• CREATE INDEX
• USER_TAB_COLUMNS
• USER_TAB_COL_STATISTICS
• DBMS_STATS.GATHER_TABLE_STATS
• DBMS_SPACE.CREATE_INDEX_COST
• Oracle B-Tree Index Internals (Oracle Concepts Guide)