♣
题目部分
在Oracle中，如何预估即将创建索引的大小？
♣
答案部分
如果当前表大小是1TB，那么在某一列上创建索引的话索引大概占用多大的空间？对于这个问题，Oracle提供了2种可以预估将要创建的索引大小的办法：
① 利用系统包DBMS_SPACE.CREATE_INDEX_COST直接得到。利用DBMS_SPACE.CREATE_TABLE_COST可以获得将要创建的表的大小。
② 利用Oracle 11g新特性NOTE RAISED WHEN EXPLAIN PLAN FOR CREATE INDEX。
下面分别举例说明。
数据库版本为Oracle 11.2.0.3，实验过程如下所示：
代码语言：javascript复制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.
第一种办法：DBMS_SPACE.CREATE_INDEX_COST
代码语言：javascript复制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.
说明：USED_BYTES代表实际使用的字节数，而ALLOCATED代表申请的字节数。
第二种办法：Oracle 11g新特性：NOTE RAISED WHEN EXPLAIN PLAN FOR CREATE INDEX
这是一个非常实用的小特性，在Oracle 11gR2中使用EXPLAIN PLAN FOR CREATE INDEX时，Oracle会提示评估的索引大小（ESTIMATED INDEX SIZE）了：
代码语言：javascript复制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.
创建真实索引查看占用的字节数：
代码语言：javascript复制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
从上面的内容可以看到，两种办法给出的索引评估大小与实际索引占用空间大约都为2M，所以，差别并不大，但这里有个前提条件就是预估索引大小之前必须对表进行分析过。
& 说明：
有关如何预估即将创建索引的大小可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-1381160/
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
