11g新特性:Note raised when explain plan for create index

这是一个挺实用的小特性，在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给出的索引评估大小与实际索引占用空间差别不大 */