Saturday, October 1, 2011

How to estimate the size of the index in oracle

In Oracle , there are few ways to estimate the size of the index , that you are planning to create.
Let us see an example .


Method 1:
SQL> drop table t purge ;
Table dropped.

SQL> create table t as select object_id , object_name , object_type from all_objects ;
Table created.

SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.

SQL> explain plan for create index idx1_t on t(object_id);
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
------------------------------
Plan hash value: 2847741357
---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |        | 77233 |   377K|   170   (1)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| IDX1_T |       |       |            |          |
|   2 |   SORT CREATE INDEX    |        | 77233 |   377K|            |          |
|   3 |    TABLE ACCESS FULL   | T      | 77233 |   377K|   130   (1)| 00:00:01 |
---------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
------------------------------
Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing
   - estimated index size: 2097K bytes
15 rows selected.


Method 2: 

SQL> variable u_byte number;
SQL> variable a_byte number;
SQL> exec dbms_space.create_index_cost( 'create index idx1_t on t(object_id)', :u_byte, :a_byte );
PL/SQL procedure successfully completed.

SQL> print :u_byte;
    U_BYTE
----------
    386165

SQL> print :a_byte;
    A_BYTE
----------
   2097152



As you can see , with the second method , the estimated size are not rounded . Being this is an estimate , this shouldn't matter.