这是一个挺实用的小特性，在11g r2中使用explain plan for create index时Oracle会提示评估的索引大小(estimated index size)了：
SQL> set linesize 200 pagesize 1400;
SQL> explain plan for create index ind_t23 on maclean(t2,t3);
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 2510282917
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 1000K| 9765K| 1267 (1)| 00:00:16 |
| 1 | INDEX BUILD NON UNIQUE| IND_T23 | | | | |
| 2 | SORT CREATE INDEX | | 1000K| 9765K| | |
| 3 | TABLE ACCESS FULL | MACLEAN | 1000K| 9765K| 760 (2)| 00:00:10 |
----------------------------------------------------------------------------------
Note
-----
- estimated index size: 24M bytes
14 rows selected.
SQL> create index ind_t23 on maclean(t2,t3);
Index created.
SQL> select bytes/1024/1024 from dba_segments where segment_name='IND_T23';
BYTES/1024/1024
---------------
23
SQL> analyze index ind_t23 validate structure;
Index analyzed.
SQL> select btree_space/1024/1024,used_space/1024/1024 from index_stats;
BTREE_SPACE/1024/1024 USED_SPACE/1024/1024
--------------------- --------------------
22.3849487 20.0912952
/* 可以看到 explain plan给出的索引评估大小与实际索引占用空间差别不大 */
