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