Oracle超详细笔记6--索引创建及管理

1.      Oracle 索引简介

在 Oracle 数据库中，存储的每一行数据都有一个 rowID 来标识。当 Oracle 中存储着大量的数据时，意味着有大量的rowID ，此时想要快速定位指定的 rowID ，就需要使用索引对象。

当对 Oracle 表执行指定条件的查询时，常规的方法是将所有的记录取出来，然后再把每一条记录与查询条件作对比，最后返回满足条件的记录。这样操作不仅耗费时间并耗费资源。当有了索引之后，只需要在索引中找到符合查询条件的索引字段值，就可以通过保存在索引中的 rowID 快速找到表中对应的记录。

用户可以根据情况不同创建多种类型的索引。

 

常用的索引类型有：

B树索引

反向键索引
位图索引

基于函数的索引

簇索引

全局和局部索引等

 

创建索引时需要注意以下几点：

索引应该建立在 where 子句频繁引用、排序以及分组的列上，如果选择的列不合适将无法提升查询速度；

限制索引的个数。索引只要提升查询速度，但会降低 DML 操作的速度；

指定索引块空间的使用参数。基于表建立索引时， Oracle 会将相应表添加到索引块。为索引添加数据时， Oracle 会按照 pctfree 参数在索引块上预留部分空间。如果将来在表上执行大量的 insert 操作，那么应该在建立索引时设置较大的pctfree ；

将表和索引部署到相同的表空间，可以简化表空间的管理；将表和索引部署到不同的表空间，可以提高访问性能（Oracle 能够并行读取不同硬盘的数据）；

 

2.      创建索引

       创建索引的基本语法：

CREATE UNIQUE.BITMAP INDEX [schema.] index
ON [schema.] table

(column [ ASC | DESC ] [ , column [ASC | DESC ] ] ...)
[ TABLESPACE tablespace ]

[ PCTFREE integer ]

[ INITRANS integer ]

  [ MAXTRANS integer ]

  [ storage-clause ]

  [ LOGGING| NOLOGGING ]

  [ NOSORT ]

 

语法选项如下：

UNIQUE：用于指定唯一的索引（缺省为Nonunique）

Schema：索引/表的所有者

Index：索引名

Table：表名

Column：列名

ASC/DESC：指示是按升序还是按降序创建索引

TABLESPACE：指定要在其中创建索引的表空间

PCTFREE：创建索引时为容纳新的索引项而在每块中保留的空间大小（以总空间量减去块头后的百分比表示）

INITRANS：指定每块中预先分配的事务处理项的数目（缺省值和最小值为 2。）

MAXTRANS：限制可以为每个块分配的事务处理项数（缺省值为 255。）

STORAGE 子句：标识确定如何为索引分配区的存储子句

LOGGING：指定在重做日志文件中记录索引创建操作和在索引上执行的后续操作（这是缺省值。）

NOLOGGING：指定在重做日志文件中不记录创建操作和某些类型的数据加载操作

NOSORT：指定将行按升序存储在数据库中，这样，Oracle服务器在创建索引时不必对行进行排序

 

在创建索引时， Oracle 首先对将要简历索引的字段进行排序，然后将排序后的字段值和对应记录的 rowID 存储在索引段中。查询时，根据索引查询指定条件的 rowID ，再根据 rowID 提取数据行。

用户在自己的模式中创建索引必须具有CREATE INDEX系统权限，如果想在其他模式中创建索引，必须具有CREATE ANY INDEX系统权限。

当用户为表定义主键时，系统自动默认地为该列创建一个B树索引，因此用户不能再为该主键创建B树索引。

1)B 树索引

B树索引是Oracle中默认的最常用的索引，缺省条件下建立的索引就是这种类型的索引。

B树索引是一对一的，一个索引条目指向一行。B树索引的组织结构类似一个树，由枝干块（branch block)和树叶块（leaf block)组成,枝干块包含了索引列（关键字）和另一索引的地址。树叶块包含了关键字和给表中每个匹配行的ROWID。

每个索引条目（也可以叫做每条记录）都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值；第二个字段表示所链接的索引块的地址，该地址指向下面一个索引块。

在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。

对于唯一性索引,根节点的数据值中只存在一条记录的ROWID.而对于非唯一性索引根节点的数据值中可能存在多条记录的ROWID.对于非唯一性索引,系统首先按照索引关键字对叶节点数据值进行排序,然后再按照数据记录的ROWID进行排序.

 

B树索引包含以下几类子索引：
（1）唯一性索引和非唯一性索引

（2）简单索引和复杂索引

 

例：在“产品信息”表PRODUCT的“产品名称”PNAME列上创建名为product_index的索引。

CREATE INDEX product_index

ON PRODUCT(PNAME)

TABLESPACES USERS;

 

例：在“产品信息”表PRODUCT的“产品名称”PNAME列上创建名为product_index的唯一索引。

CREATE UNIQUE INDEX product_index

ON PRODUCT(PNAME)

TABLESPACES USERS;

注意若在列上定义UNIQUE约束，ORACLE会自动为该列建立唯一索引，因此用户没有必要再为表中列创建唯一索引

 

组合索引是指在表的多个列上创建的索引，也称为“连接索引”，组合索引中的列可以按任意顺序排列，对于在WHERE子句中包含多个列的查询，可以提高数据访问速度。

例：在“职员信息”表EMP的“职员编码”列EMPNO和“部门编码”列DEPTNO上创建名为idx_emp的唯一索引。

Create unique index  idx_emp  on  emp(empno,deptno);

 

2） 位图索引

       位图索引是为数据仓库/在线分析查询环境设计的，目的是在加快查询速度时节省存储空间。通常情况下，索引都要耗费比较大的存储空间，位图采用了压缩技术实现磁盘空间缩减。位图索引适用于低基数列如果系统中的数据会由多个并发会话频繁地更新，这种系统也不适用位图索引。

位图索引是这样一种结构，其中用一个索引键条目存储指向多行的指针；这与B*树结构不同，在B*树结构中，索引键和表中的行存在着对应关系。在位图索引中，可能只有很少的索引条目，每个索引条目指向多行。而在传统的B*树中，一个索引条目就指向一行。 

适用低基数列。基数低表示在索引中，所有取值的数量比表中行的数量少.

例：在“职员信息”表EMP的“性别”列SEX和“部门编码”列DEPTNO上创建名为idx_bitmap的位图索引。

CREATE BITMAP INDEX idx_bitmap ON EMP(SEX，DEPTNO);

 

3） 反向键索引

       在单调递增的列上使用 B 树索引时，若用户对表中数据做了删除操作，将导致对某一边的叶子节点的大量占用。 Oracle提供另一种索引机制，及反向键索引，它可以将添加的数据随机分散到索引中。反向键索引是一种特殊的 B 树索引，在顺序递增数列上建立索引非常有用。反向键索引在原理和存储结构方面和 B 树索引类似。如果在将数据放在索引中之前，将先把所存储数据的字节反转，这样原本可能在索引中相邻放置的值在字节反转之后就会相距很远，通过反转字节，对索引的插入的数据就会放在多个块上，如1000,10001,10011,10111,1100经过反向后的值将是0001,1001,1101,0011。经过位反向处理的有序数据变得比较随机了，这样所得到的索引树就比较对称，从而提高表的查询性能。

在创建反向键索引时必须在CREATE TABLE语句中指定REVERSE关键字。反向键索引适合于只做添加不做修改而且严格排序的列

例：在“产品信息”表PRODUCT的“产品编码”PID列上创建名为idx_product的反向键索引。

create  index  idx_productid  

on  product(pid)  reverse

对键的反转是由系统自动处理的，用户可以使用常规的方式查询数据。      

 

4） 基于函数的索引

       用户在使用数据库时，最常遇到的问题是大小写字符敏感。例如在 emp 表中 Job 字段有 MANAGER 的记录，当用户使用小写搜索时则无法找到该记录，只能通过函数 upper 对应进行转换，在使用转换后的数据进行检查。但是这样查询是，即便job 列有普通索引， Oracle 也会执行全表搜索，并为遇到的各个行计算 upper 函数。这种情况可以使用建立基于函数的索引，通常只是常规 B 树索引，但是它存放的数据是由表中数据应用函数后得到的，而不是直接存放表中的数据本身。

在创建基于函数的索引时Oracle首先对包含索引列的函数或表达式进行求值，然后对求值后的结果进行排序，最后再存储到索引中。函数索引可以是普通的B树索引，也可以是位图索引。

为了在自己的模式中的表上创建基于函数的索引，必须有系统特权QUERY REWRITE；为了在其他模式中的表上创建基于函数的索引，必须有系统特权GLOBAL QUERY REWRITE。

如果习惯使用小写字符串，可以创建如下索引：

Create index index _ test

On emp ( lower ( job ));

 

5）簇索引

如果经常同时访问多个表，则可以把这些表物理地存放在一起，从而减少I/O操作次数，提高系统的效率。这些物理地存储在一起的表需要管理容器，这个容器就是簇。簇是一种存储表的方法，这些表密切相关并经常一起连接进磁盘的同一区域，例如，表 EMP 和DEPT的数据行可以一起插入到称为簇（Cluster）的单个区域中，而不是将两个表放在磁盘上的不同扇区上。

用户创建簇必须具有CREATE CLUSTER系统权限，如果想在其他模式中创建簇，还必须具有CREATE ANY CLUSTER系统权限。创建簇之后，用户可以在簇中创建表，也就是簇表（EMP和DEPT表），在将数据行插入簇表之前，还必须创建簇索引。

 

6）全局和局部索引

通常在对表进行分区时，也会将对应的索引进行分区。分区索引就是在每个区上单独创建索引，它能自动维护。在DROP或TRUNCATE某个分区时不影响该索引的其他分区索引的使用。分区的表可以具有未分区的索引，未分区的表也可以具有分区的索引，索引和表是相互独立的模式对象，对索引和表进行分区的目的都是为了更加容易管理与维护。

全局索引就是在全表上创建索引，它可以为索引创建自己的分区。局部索引是在各个分区表上创建的索引。在ORACLE中，一共可以为分区表建立3种类型的索引：局部分区索引、全局分区索引和全局非分区索引

 

当一个表的数据超过过2000万条或占用2G空间时，建议建立分区表。

例：创建一个名称为SALES的分区表
create table sales

(sno int,sname varchar2(16),

price number(10),

qty int ,

constraint pk_sales primary key (sno))

partition by range(sno)

(partition p1 values less than (10000),

partition p2 values less than (20000),

partition p3 values less than (30000),

partition p4 values less than (maxvalue));
创建分区表后，可以使用如下语句创建局部分区索引：

create index idx_sales on sales(sno) local (partition p1,partition p2,partition p3,partition p4);

或者 create index idx_sales_c2 on sales(sno) local ;

可以通过查询数据字典USER_INDEXES,USER_PART_INDEXES,和USER_IND_PARTITIONS视图来查看索引是否有效和索引的分区情况。

 

创建全局分区索引索引：

CREATE INDEX idx_global_sales ON sales(qty) GLOBAL PARTITION BY

Range(qty) (partition ip1 values less than(10000), partition ip2 values less

than (20000), partition ip3 values less than(maxvalues));

创建全局非分区索引：

CREATE INDEX sales_global_index ON sales(sno) GLOBAL;

 

3.      修改索引

修改索引使用 alter index 完成。

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

例：

修改索引的存储参数

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

分配和释放索引空间

ALTER INDEX idx_sales ALLOCATE EXTENT(SIZE 1M);

释放多余空间

ALTER INDEX idx_sales DEALLOCATE UNUSED;

例：合并索引

ALTER INDEX idx_sales COALESCE DEALLOCATE UNUSED;

 

重建索引则不仅可以消除存储碎片，而且可以改变索引的存储参数以及存储表空间，相当于在表空间中重新建立一个新的索引，然后再删除原来的索引。

例：对索引idx_sales进行重建索引操作

ALTER INDEX  idx_sales  REBUILD;

 

例：对索引idx_sales进行重建索引操作,同时指定存储重建索引的表空间和存储参数。

ALTER INDEX  idx_sales  REBUILD

tablespace users

storage(initial 128k

next 64k

maxextents 10)

对全局分区索引的一个分区索引进行重建

ALTER INDEX  idx_sales  REBUILD partition p1;

 

重建索引具有以下特点：

1. 将现有索引作为数据源建立新索引。使用现有索引建立索引时无需排序，从而使性能更佳。
2. 在建立新索引后，删除旧索引。在重建期间，各自的表空间内需要有足够的空间以容纳新、旧索引。
3. 重建索引中不包括任何已删除的项。因此，该索引可以更有效地使用空间。
4. 在建立新索引的过程中，查询可继续使用现有索引。
5. 需要将现有索引移到另外的表空间中需重建索引，不能在临时表中重建索引。
6. 不能重建整个分区索引，必须分别重建每个分区或子分区。
7. 重建索引过程中不能回收未用空间，不能整个更改索引的PCTFREE参数值。

 

4.      删除索引

       删除索引使用 drop index 语句，如果要删除其他模式中的索引，必须具有DROP ANY INDEX系统权限。当索引过于碎片化，或者不经常被用到时，既可以删除索引：

Drop index index _ test ；

       注：删除表是也会删除其相应的索引。

 

5.      显示索引信息

       为了显示索引的信息， Oracle 提供了一系列数据字典视图，使用户了解索引的各方面信息。

1） 显示表的所有索引：显示 emp 的所有索引

Select * from dba _ indexs where owner =’ EMP ’

2） 显示索引列：显示 index _ test 所使用的索引列：

Select * from user _ ind _ columns where index _ name =’ INDEX _ TEST ’

3） 显示index _ test所使用的索引位置及大小

Select * from user _ segments where segment _ name =’ INDEX _ TEST ’

4） 显示index _ test所使用的函数索引

Select * from user _ ind _ expressions where index _ name =’ INDEX _ TEST 

 

6. 监视索引 

从 Oracle9i 开始，可以在 V$OBJECT_USAGE 中收集和显示有关索引使用的统计信息。

要开始监视索引的使用，可执行以下语句：

ALTER INDEX hr.idx_sales

MONITORING USAGE

要停止监视索引的使用，可执行以下语句：

ALTER INDEX hr.idx_sales

NOMONITORING USAGE