【DB筆試面試561】在Oracle中，如何預估即將創建索引的大小？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在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，實驗過程如下所示：
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
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）了：
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.
創建真實索引查看佔用的位元組數：
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程式設計師面試筆試寶典》，作者：李華榮。
