In my last post about temporary tablespace, I talked about monitoring the usage during an index creation since it is one of activties which need sort segments. One question likely arises is how to estimate the size of an index so that we can add enough temp data files and data files ahead?

If you have a test envrionment with the similar size to the production database, you can just create one in that envrionment. Or if the size differs significantly, you can export the table invovled from the production and import it into the test.

Or you can just do the estimation on the production system with the following two ways:

1. Gather table stats and check explain plan for the index creation statement:

SQL> exec dbms_stats.gather_table_stats(ownname=>joe, tabname=>'CUSTOMER');
 
PL/SQL procedure successfully completed.

SQL> explain plan for  CREATE INDEX JOE.IDX_ALT_USER_ID ON CUSTOMER (LOWER(ALT_USER_ID)) TABLESPACE TS_INDEX ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 2322690384

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |                   |  1150M|   139G|    12M  (1)| 00:07:58 |
|   1 |  INDEX BUILD NON UNIQUE| IDX_ALT_USER_ID   |       |       |            |          |
|   2 |   SORT CREATE INDEX    |                   |  1150M|   139G|            |          |
|   3 |    TABLE ACCESS FULL   | CUSTOMER          |  1150M|   139G|  8281K  (1)| 00:05:24 |
--------------------------------------------------------------------------------------------

Note
-----
   - estimated index size: 188G bytes

So you see Oracle estimates 188G will be needed for this index.

2. Use the procedure dbms_space.create_index_cost

exec dbms_stats.gather_table_stats(ownname=>joe, tabname=>'CUSTOMER');
   
set serverout on
variable used_bytes number 
variable alloc_bytes number
variable used_gb number
exec dbms_space.create_index_cost( 'CREATE INDEX JOE.IDX_ALT_USER_ID ON CUSTOMER (LOWER(ALT_USER_ID)) TABLESPACE TS_INDEX', :used_bytes, :alloc_bytes ); 
print :used_bytes 
print :alloc_bytes 
select :used_bytes/1024/1024/1024 from dual;

As long as the table statistics is up to date and compression is not used, the actual size will be very close to the estimated value from the 2 ways above.

• Oracle