- 了解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;