This article presents simple method how you can estimate index size for CREATE REBUILD operation

Prepare test table with dummy data

CREATE TABLE test_tbl
(
  id1 NUMBER,
  id2 NUMBER,
  id3 NUMBER,
  id4 NUMBER
);

INSERT /*+ APPEND */ INTO test_tbl
SELECT LEVEL, 2, mod(LEVEL, 100), mod(LEVEL, 10)
FROM DUAL
CONNECT BY LEVEL < 1000000;

COMMIT;

SELECT count(*) FROM test_tbl;

  COUNT(*)
----------
    999999

To check estimated size for new index execute following command. It’s not correct estimation because statistics on the table are not correct !!!

EXPLAIN PLAN FOR
CREATE INDEX test_idx ON test_tbl(id1);

SELECT * FROM TABLE(dbms_xplan.display(null,null,'BASIC +NOTE'));

Plan hash value: 21275375
 
-------------------------------------------
| Id  | Operation              | Name     |
-------------------------------------------
|   0 | CREATE INDEX STATEMENT |          |
|   1 |  INDEX BUILD NON UNIQUE| TEST_IDX |
|   2 |   SORT CREATE INDEX    |          |
|   3 |    TABLE ACCESS FULL   | TEST_TBL |
-------------------------------------------
 
Note
-----
   - estimated index size: 65536  bytes

collect statistics on the table to have correct estimates

EXEC dbms_stats.gather_table_stats(USER, 'TEST_TBL');

Now calculations will be fine

EXPLAIN PLAN FOR
CREATE INDEX test_idx ON test_tbl(id1);

SELECT * FROM TABLE(dbms_xplan.display(null,null,'BASIC +NOTE'));

Plan hash value: 21275375
 
-------------------------------------------
| Id  | Operation              | Name     |
-------------------------------------------
|   0 | CREATE INDEX STATEMENT |          |
|   1 |  INDEX BUILD NON UNIQUE| TEST_IDX |
|   2 |   SORT CREATE INDEX    |          |
|   3 |    TABLE ACCESS FULL   | TEST_TBL |
-------------------------------------------
 
Note
-----
   - estimated index size: 24M bytes

Have a fun 🙂

Tomasz