了解Maclean Liu|向Maclean Liu提问 Oracle ALLSTARS 全明星(群内有多位Oracle高级售后support,N位OCM和ACE) QQ群 # QQ群号:23549328 # 已经升级到 2000人群,空位多多。欢迎有一定基础的Oracle骨友加入,现在入群需要经过Maclean的技术面试,欢迎面试,请加QQ号:47079569 为好友参加面试 2群基础群 适合刚入门的同学,会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群,空位多多,无需面试

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给出的索引评估大小与实际索引占用空间差别不大 */

posted on 2010-12-07 20:58  Oracle和MySQL  阅读(301)  评论(0编辑  收藏  举报

导航