Oracle 11g 預(yù)估索引大小

一、創(chuàng)建索引注意事項(xiàng)

1、預(yù)估索引大小

2、查看索引表空間可用空間大小

3、查看臨時(shí)表空間大小

4、業(yè)務(wù)空閑期創(chuàng)建

5、可以考慮在線(xiàn)、并行、nologging模式

二、預(yù)估索引方法

操作步驟如下：

SQL> explain plan for create index idx_object_id on tt(object_id) ;

Explained.

SQL> set lines 200 pages 1000

SQL> select * from table(dbms_xplan.display());                

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3757763116

----------------------------------------------------------------------------------------

| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | CREATE INDEX STATEMENT |               |  2762K|    13M| 22958   (1)| 00:04:36 |

|   1 |  INDEX BUILD NON UNIQUE| IDX_OBJECT_ID |       |       |            |          |

|   2 |   SORT CREATE INDEX    |               |  2762K|    13M|            |          |

|   3 |    TABLE ACCESS FULL   | TT            |  2762K|    13M| 21560   (1)| 00:04:19 |

----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note

-----

   - estimated index size: 67M bytes

14 rows selected.

Elapsed: 00:00:00.01

SQL>