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 wner='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