ORACLE索引的日常維護

為提高查詢的效率而引入索引概念。在索引記錄中存有索引關鍵字和指向表中真正資料的指針（位址），可以存在與表不同的表空間。

索引分為：B-樹索引和位圖索引。

B-樹索引：适合于聯機事務處理系統，因為DML操作比較頻繁。

位圖索引：适用于資料倉庫系統，因為查詢較為複雜。

建立索引的原則：

1.       平衡查詢和DML的需要。索引雖然可以提高查詢的速度，但是卻降低了DML操作速度。是以在DML頻繁的表上盡量減少索引的使用。

2.       将其放入單獨的表空間。不要與表、臨時段、還原段放入同一個表空間。因為會競争I/O。

3.       使用統一的EXTENT尺寸。（表空間minimum extent的尺寸）。目的是減少系統轉換時間。

4.       大索引考慮使用nologging。

5.       索引的initrans參數通常比相對應表的高。

查詢索引：

查詢在BPMMES使用者的索引基于的表、所在的表空間、索引的類型、索引的狀态：

select index_name,table_name,tablespace_name,index_type,uniqueness,status

from dba_indexes

where owner='BPMMES'

注：INDEX_TYPE列的ONRMAL為正常。

BPMMES使用者的索引所基于的表和列的資訊：

select index_name,table_name,column_name,index_owner,table_owner

from dba_ind_columns

where table_owner='BPMMES'

建立索引：

建正常索引：

create index bpmmes.account_cus_lot_idx

on bpmmes.wms_account(customer_lot_id)

pctfree 20   --建立索引時，每一個塊中預留的空間。

storage(initial 100k

        next 100k

        pctincrease 0

        maxextents 100)

tablespace bpmmes;

建位圖索引：

create bitmap bpmmes.account_cus_lot_idx

on bpmmes.wms_account(customer_lot_id)

pctfree 20   --建立索引時，每一個塊中預留的空間。

storage(initial 100k

        next 100k

        pctincrease 0

        maxextents 100)

tablespace bpmmes;

重建索引：

alter index bpmmes.account_cus_lot_idx rebuild

收回bpmmes使用者下的account_cus_lot_idx索引段的沒用的磁盤空間：

alter index bpmmes.account_cus_lot_idx deallocate unused;

合并bpmmes使用者下的account_cus_lot_idx索引段中的碎片：

alter index bpmmes.account_cus_lot_idx coalesce

檢查索引是否用過：

1.       開啟對索引的監督

alter index bpmmes.account_cus_lot_idx monitoring usage;

2.       查詢

select customer_lot_id from wms_account

3.       查詢索引的使用情況

select * from v$object_usage

4.       關閉索引的監督

alter index bpmmes.account_cus_lot_idx nomonitoring usage;

5.       查詢索引的使用情況

select * from v$object_usage

删除索引：

drop index bpmmes.account_cus_lot_idx

來源：http://space.itpub.net/295691/viewspace-369061