Oracle estimate index size for create rebuild operation

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

Leave a Reply

Your email address will not be published. Required fields are marked *