18.管理索引

索引是用于加速数据存取的数据库对象,通过使用索引,可以降低I/O次数,提高数据访问性能.索引有多种分类方法,按照索引数据的存取方式,可以分为B*-索引,反向索引和位图索引.

按照索引列的个数,可以分为单列索引和复合索引;

按照索引列值的唯一性,可以分为唯一索引和非唯一索引.

1,单列索引和复合索引

单列索引是基于单个列所建立的索引;复合索引是基于两列或多列所建立的索引.注意,B*-树索引的个数不能超过32列,位图索引列的个数不能超过30列.可以在同一张表上建立多个索引,但要求列的组合必须不同.

CREATE INDEX emp_idx1 ON emp (ename,job);

2,唯一索引和非唯一索引

唯一索引是索引列值不能重复的索引;非唯一索引是索引列值可以重复的索引.无论是唯一索引还是非唯一索引,索引列都允许NULL,注意,当定义主键约束或唯一约束时,oracle会自动在相应的约束上建立唯一索引.

3索引管理的指导方针.

1. 装载数据后重建索引

2. 索引正确的表和列,建立和规划索引时,必须选择合适的表和列.如果选择的表和列不合适,不仅无法提供查询速度,反而会极大地降低DML操作的速度.建立索引的指导方针如下:

索引应该建立在WHERE 子句经常引用的表列上.

为了提供多表连接的性能,应该在连接列上建立索引

不用在小表上建立索引

如果经常需要基于某列或某几列执行排序操作,那么通过在这些列上建立索引,可以加快数据排序的速度.

3. 限制索引的个数.索引主要用于加速查询速度,但会降低DML操作的速度.索引越多,DML操作的速度越慢,尤其会极大的影响INSERT操作和DELETE操作的速度.因此,规划索引时,必须仔细权衡查询和DML的需求.

4. 删除不再需要的索引

5. 指定索引块空间使用参数

基于表建立索引时,oracle会将相应表列数据添加到索引块.当为索引块添加数据时,oracle会在索引块上预留部分空间(按照PCTFREE确定),该预留空间是为将来的INSERT操作准备的,如果将来会在表上执行大量INSERT操作,那么应该在建立索引时设置较大的PCTFREE.注意,建立索引时不能指定PCTUSED关键字.

6. 指定索引所在的表空间

7. 考虑并行建立索引

使用并行建立索引,多个服务器进程会共同建立索引,这样可以加快索引的建立速度.但可能会占用更多的磁盘空间

8. 使用NOLOGGING选项建立索引

当在大表上建立索引时,使用NOLOGGING选项可以最小话重做记录.使用NOLOGGING选项有以下优点.

节省重做日志空间

降低索引建立实际

提高索引并行建立的性能.

建立B*-树索引

B*-树索引是以B*-树结构组织并存放索引数据的,它是最常用的索引类型,默认情况下索引数据是以升序方式排列的.如果在WHERE子句中经常要引用某列或某几列,应该基于这些列建立B*-索引.

建立位图索引

位图索引以位值标识索引行数据,它主要用于在DDS(Decision Support System)系统中执行数据统计,数据汇总等操作.

B*-书索引建立在重复值很少的列上,而位图索引建立在重复值很多,不同值相对固定的列上.在不同值相对固定的列上,使用位图索引可以节省大量磁盘空间,其占用空间仅仅是在相同列上建立B*-索引空间的1/20~1/10.

建立位图索引

CREATE BITMAP INDEX idx_gender

ON customers(cust_gender);

初始化参数CREATE_BITMAP_AREA_SIZE用于指定建立位图索引时分配的位图区尺寸,默认为8M,改参数值越大,建立位图索引的速度越快.为了加快位图索引建立速度,应将改参数这只为更大值.该参数是静态参数,修改后必须重新启动数据库才可以生效.

ALTER SYSTEM SET create_bitmap_area_size=8388608 SCOPE=SPFILE;

方向索引是索引列值按照相反顺序存放的索引.在顺序递增的列(如ID列)上建立普通B*-树索引是,如果表的数据量非常大,将导致索引数据分别不均(偏想某个方向),为了避免出现这种情况,应在顺序递增的列上建立反向索引.需要注意,普通B*-索引既适用于范围查询,也适用于等值查询,而反向索引只适用于等值查询.

CREATE INDEX customers_pk ON customers(cust_id) REVERSE;

建立函数索引

函数索引是基于函数或表达式建立的索引.如果经常在where子句中引用函数或表达式,那么应该基于函数或表达式监视函数索引,需要注意,建立函数索引要求用户必须具有QUERY REWRITE系统权限

CREATE INDEX idx_fname ON customers(lower(cust_first_name));

修改索引

1,修改索引段存储参数.

建立索引时,oracle会为索引分配相应的索引段,如果索引段存储参数不合适,可以使用ALTER INDEX命令修改其存储参数,需要注意,存储参数INITIAL,MINEXTENTS是不能修改的.而修改其他存储窜数只对新分配的区起作用.

ALTER INDEX upper_ename STORAGE(NEXT 200K MAXEXTENTS 50);

2,分配和释放索引空间

ALTER INDEX department_dname ALLOCATE EXTENT(SIZE 1M);

释放多余空间

ALTER INDEX department_dname DEALLOCATE UNUSED;

3,重建索引.

ALTER INDEX department_dname REBUILD;

4,联机重建索引

ALTER INDEX department_dname REBUILD ONLINE;

5合并索引

ALTER INDEX department_dname COALESCE;

删除索引

DROP INDEX department_dname;

显示索引信息

1,显示表的所有索引

DBA_INDEXES显示数据库的所有索引

ALL_INDEXES显示数据库当前用户可以访问的所有索引

USER_INDEXES显示数据库当前用户的所有索引

2,显示索引列

DBA_IND_COLUMNS显示数据库所有索引的表的信息

ALL_IND_COLUMNS

USER_IND_COLUMNS

3,显示索引段位置及尺寸

DBA_SEGMENTS可以显示数据库索引段的信息

USER_SEGMENTS显示当前用户段的信息.

4,显示函数索引

DBA_IND_EXPRESSIONS显示数据库索引函数索引所对应的函数或表达式

ALL_IND_EXPRESSIONS

USER_IND_EXPRESSIONS