oracle Provides 2 A way to estimate the size of the index to be created :

 ①  Using bags Dbms_space.create_index_cost Directly obtained

 ②  utilize 11g New characteristics Note raised when explain plan for create index



1、 Environmental statement

[[email protected]_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 26 15:58:06 2014

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

SQL> create table test_index_size as select * from dba_objects;

 

Table created.

 

SQL>  EXEC DBMS_STATS.gather_table_stats(ownname => 'SYS',tabname => 'TEST_INDEX_SIZE');

PL/SQL procedure successfully completed.

 


The first way : Dbms_space.create_index_cost

Script :

declare

  l_index_ddl       varchar2(1000);

  l_used_bytes      number;

  l_allocated_bytes number;

begin

  dbms_space.create_index_cost(ddl         => 'create index idx_t on sys.test_index_size(object_id) ',

                               used_bytes  => l_used_bytes,

                               alloc_bytes => l_allocated_bytes);

  dbms_output.put_line('used= ' || l_used_bytes || 'bytes' ||

                       '     allocated= ' || l_allocated_bytes || 'bytes');

end;

/

experiment :


SQL> set serveroutput on
SQL> declare

  2    l_index_ddl varchar2(1000);

  3    l_used_bytes number;

  4    l_allocated_bytes number;

  5  begin

  6    dbms_space.create_index_cost(ddl => 'create index idx_t on sys.test_index_size(object_id) ',

  7      used_bytes => l_used_bytes,

  8      alloc_bytes => l_allocated_bytes);

  9    dbms_output.put_line('used= ' || l_used_bytes || 'bytes' ||

10      '   allocated= ' || l_allocated_bytes || 'bytes');

11  end;

12  /

used= 383105bytes     allocated= 2097152bytes

PL/SQL procedure successfully completed.

 

SQL>


PL/SQL Process completed successfully .
explain <font lucida"="" console?="" style="word-wrap: break-word;">:  used_bytes   Give the actual number of bytes of index data .
      allocated Is the number of bytes actually occupied in the table space when the index is actually created . 

 

The second way :11g New characteristics :Note raised when explain plan for create index

 

This is a very practical little feature , stay 11g r2 Use in Oracle The index size of the evaluation will be prompted <font times"="" new="" roman?="" style="word-wrap: break-word;">(estimated index size) 了 :

SQL> set linesize 200 pagesize 1400;

SQL>  explain plan for create index idx_t on sys.test_index_size(object_id) ;

 

Explained.

 

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

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------

Plan hash value: 32582980

 

--------------------------------------------------------------------------------

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time   |

--------------------------------------------------------------------------------

|   0 | CREATE INDEX STATEMENT |       | 76621 |   374K| 350(1)| 00:00:05 |

|   1 |  INDEX BUILD NON UNIQUE| IDX_T |       |       |       |          |

|   2 |   SORT CREATE INDEX    |       | 76621 |   374K|       |          |

|   3 |    INDEX FAST FULL SCAN| IDX_T |       |       |       |          |

--------------------------------------------------------------------------------

 

Note

-----

   - estimated index size: 2097K bytes

 

14 rows selected.

 



Create real index

SQL> create index idx_t on sys.test_index_size(object_id) ;

 

Index created.

SQL>  analyze index IDX_T validate structure;

 

Index analyzed.

SQL> select bytes from dba_segments where segment_name='IDX_T';

 

     BYTES

----------

   2097152

 

You can see 2 There is little difference between the size of index evaluation given by the two methods and the space occupied by the actual index , But there is a prerequisite here that the table must be analyzed before the index size is estimated .