十五、索引

• 了解oracle11g的索引的分类
• 理解oracle11g的索引查找的原理
• 能够根据情况建立合适的索引

索引

• 索引是与表相关的一个可选结构

• 用以提高 SQL 语句执行的性能

• 减少磁盘I/O

• 使用 CREATE INDEX 语句创建索引

• 在逻辑上和物理上都独立于表的数据

• Oracle 自jh动维护索引

• 索引分为：B树索引（平衡树索引）、位图索引。

• B树索引分为：唯一索引、组合索引、反向键索引、基于函数的索引

创建标准索引

SQL> CREATE INDEX item_index ON itemfile (itemcode)TABLESPACE index_tbs;

查看当前用户创建的索引

查看所有列信息

分析索引

analyze index <index_name> validate structure;

查看index_stats表中的pct_used列的值，如果pct_used的值过低，说明在索引中存在碎片，可以重建索引，来提高pct_used的值，减少索引中的碎片。

重建索引

唯一索引

• 唯一索引确保在定义索引的列中没有重复值
• Oracle 自动在表的主键列上创建唯一索引
• 使用CREATE UNIQUE INDEX语句创建唯一索引

SQL> CREATE UNIQUE INDEX item_index ON itemfile (itemcode);

组合索引

• 组合索引是在表的多个列上创建的索引
• 索引中列的顺序是任意的
• 如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度

反向键索引

• 反向键索引反转索引列键值的每个字节
• 通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上
• 创建索引时使用REVERSE关键字

SQL> CREATE INDEX rev_index ON itemfile (itemcode) REVERSE;
SQL> ALTER INDEX rev_index REBUID NOREVERSE;

位图索引

• 位图索引适合创建在低基数列上(男/女)
• 位图索引不直接存储ROWID，而是存储字节位到ROWID的映射
• 节省空间占用
• 如果索引列被经常更新的话，不适合建立位图索引
• 总体来说，位图索引适合于数据仓库中，不适合OLTP中

SQL> CREATE BITMAP INDEX bit_index ON order_master (orderno);

基于函数索引

• 基于一个或多个列上的函数或表达式创建的索引
• 表达式中不能出现聚合函数
• 不能在LOB类型的列上创建
• 创建时必须具有 QUERY REWRITE 权限

SQL> CREATE INDEX lowercase_idx ON toys (LOWER(toyname));
SQL> SELECT toyid FROM toys WHERE LOWER(toyname)='doll';

重建索引

ALTER INDEX index_name REBUILD [ONLINE] [NOLOGGING] [COMPUTE STATISTICS];

其中：ONLINE使得在重建索引过程中，用户可用对原来的索引进行修改；

NOLOGGING表示在重建过程中产生最少的重做条目redo Entry；

COMPUTE STATISTICS表示在重建过程中就生成了oracle优化器所需的统计信息，避免了索引重建之后再进行analyze或dbms_stats来收集统计信息。

删除索引

SQL> DROP INDEX item_index;

索引的分区

• 可以将索引存储在不同的分区中

• 与分区有关的索引有三种类型：

  • 局部分区索引 － 在分区表上创建的索引，在每个表分区上创建独立的索引，索引的分区范围与表一致

    

    

    

    

  • 全局分区索引 － 在分区表或非分区表上创建的索引，索引单独指定分区的范围，与表的分区范围或是否分区无关

    

  • 全局非分区索引 － 在分区表上创建的全局普通索引，索引没有被分区

获取索引的信息

• 与索引有关的数据字典视图有：
  • USER_INDEXES － 用户创建的索引的信息
  • USER_IND_PARTITIONS － 用户创建的分区索引的信息
  • USER_IND_COLUMNS － 与索引相关的表列的信息

SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS ORDER BY INDEX_NAME, COLUMN_POSITION;