Showing posts with label Index Size Estimate. Show all posts
Showing posts with label Index Size Estimate. Show all posts

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.