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.

Honey, I shrunk the table!

Let us say , you  have a table where there are frequent deletes / inserts  and your application reads this table ( via full table scan )  for processing the business results . You are  likely  to have performance issue , as Oracle has to read all the empty blocks till HWM .  In 10g and above , you can reset the HWM as below .

SQL> drop table t ;
Table dropped
SQL> create table t as select * from all_objects;
Table created
SQL> select sum(bytes) from user_segments where segment_name = 'T';
SUM(BYTES)
----------
99614720


SQL> select count(*) from t;
COUNT(*)
----------
629079


SQL> delete from t where owner ='SYS';
23157 rows deleted

SQL> select count(*) from t;
COUNT(*)
----------
605922


SQL> select sum(bytes) from user_segments where segment_name = 'T';
SUM(BYTES)
----------
99614720



Even after deleting few records , the space is not released.
But , when you shrink the table as below , you can reclaim the space.

 

SQL> alter table t ENABLE row movement ;
Table altered
SQL> alter table t shrink space compact;
Table altered
SQL> alter table t shrink space;
Table altered

SQL> select sum(bytes) from user_segments where segment_name = 'T';
SUM(BYTES)
----------
88932352

If you are not sure of which objects can be the candiates for shrinking , you can refer to the findings from segment advisor . Either you can use segment advisor from EM or PL /SQL . You can get the relevant sql commands as shown below .


Select * from

(
select c3 from table (dbms_space.asa_recommendations())
union ALL
select c2 from table (dbms_space.asa_recommendations())
union all
select c1 from table (dbms_space.asa_recommendations())
)
where c3 is not null

dbms_space.asa_recommendations()  provides the estimated space savings . I have asked the accuracy of this value to Tom Kyte in his forum . Please see his response at
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2049277600346543592#2049993400346447818

Please send in your comments.