索引的概念
索引是一个可选的结构、与簇表和表相关联，可以提高读取数据的速度，可以在表的一列或多列上创建索引，索引直接指向包含所查询值的行的位置，减少磁盘I/O，与索引的表时相互独立的物理结构，Oracle自动使用并维护索引，插入、删除、更新表后，自动更新。如果堆表没有索引,那么数据库必须执行全表扫描来找到一个值

索引的创建语法
CREATE UNIUQE | BITMAP INDEX .
      ON .
          (|expression>ASC|DESC,
          |expression>ASC|DESC,...)
      TABLESPACE
      STORAGE
      LOGGING|NOLOGGING
      COMPUTE STATISTICS
      NOCOMPRESS|COMPRESS
      NOSORT|REVERSE
      PARTITION|GLOBAL PARTITION

相关说明
1）UNIQUE|BITMAP:指定UNIQUE为唯一索引，BITMAP为位图索引，省略为B-Tree索引。
2）>|expression>ASC|DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”
3）TABLESPACE：指定存放索引的表空间（索引和原表不在一个表空间时效率更高）
4）STORAGE：设置表空间的存储参数
5）LOGGING | NOLOGGING：是否对索引产生重做日志（对大表尽量使用NOLOGGING来减少占用空间并提高效率 )
6）COMPUTE STATISTICS：创建新索引时收集统计信息
7）NOCOMPRESS|COMPRESS：是否使用“键压缩”（使用键压缩可以删除一个键列中出现的重复值）
8）NOSORT|REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值
9）PARTITION|NOPARTITION：可以在分区表和未分区表上对创建索引进行分区

索引的特点：

1）通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性
2）可以大大加快数据的检索速度，
3）可以加速表和表之间的连接，特别是在实现数据的参考完整性
4）使用分组和排序子句进行数据检索时，可以减少查询中分组和排序的时间
5）通过使用索引，在查询过程中，使用优化隐藏器，提高系统的性能。

索引不足
1）创建和维护索引要耗费时间，这种时间随着数据量的增加而增加
2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，需要的空间更大
3）当对表中的数据进行增加、删除、修改的时候，索引要动态维护，降低了数据的维护速度。

建立索引列的特点
1）经常需要搜索的列
2）作为主键的列，强制该列的唯一性和组织表中数据的排列结构
3）经常用在连接的列上，加速连接
4）经常根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
5）经常需啊哟排序的列上创建索引，因为索引已经排序，查询可以利用索引的排序，加快排序查询时间
6）经常使用在WHERE子句中的列上创建索引，加快条件判断速度。

不应该创建索引列的特点
1）在查询中很少使用或者参考的列不应该创建索引
2）只有很少数据值的列
3）对于定义为blob数据类型的列不应该增加索引
4）修改性能远远大于检索性能时

限制索引
1）使用不等于操作符（<>,!=）
2）使用IS NULL 或 IS NOT NULL 如果索引列在某些行中存在NULL值，就不会使用这个索引（不包括位图索引）
3）使用函数，如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在的索引的列使用函数时，会使优化器忽略掉这些索引。
4）比较不匹配的数据类型
例：abc_number是一个varchar2类型，在abc_number字段上有索引。
      下面语句将执行全表扫描：
      select a,b,c,d,e from A where abc_number=123456;
      Oracle会自动把where子句变成to_number(abc_number)=123456
      限制了索引的使用，改为以下查询可以使用索引：
select a,b,c,d,e from A where abc_number='123456';
        不匹配的数据类型之间比较会让Oracle自动限制索引的使用

查询索引：DBA_INDEXES, USER_INDEXES, USER_IND_COLUMNS

Oracle rowid
Oracle通过每个行的rowid，Oracle索引提供了访问单行数据的能力，ROWID直接指向单独行的线路图

SQL> select rowid from dual;

ROWID
------------------
AAAAEC AAB AAAAgi AAA

rowid=对象编号（6）+文件编号（3）+块编号（6）+ 行编号（3）=18位

ROWID的格式如下：
对象编号      文件编号      块编号      行编号
AAAAEC        AAB           AAAAgi        AAA

索引分类

B树索引（默认类型）
位图索引
HASH索引
索引组织表索引
反转键（reverse key）索引
基于函数的索引
分区索引
位图连接索引

B树索引（默认类型）
B树索引在 Oracle中是一个通用索引。在创建时它就默认的类型。B树索引可以是一个列的（简单）索引，也可以是组合/复合（多个列）索引。 B树索引最多可以包括 32列
B树索引包括树枝块和树叶块
树枝块：包含链中下一个块的ID号
树叶块：包含了索引值、ROWID、以及指向前一个和后一个树叶块的指针。

下图为B树索引的结构图，

B-tree特点：
适合与大陆的增、删、改（OLTP）
不能用包含OR操作符的查询
适合高基数的列（唯一值多）
典型的树状结构
每个节点都是数据块

位图索引
位图索引适用于离散度较低的列，它的叶块中存放key, start rowid-end rowid,并应用一个函数把位图中相应key值置1，位图索引在逻辑or时效率最高。

例：
SQL>create bitmap index job_bitmap on emp1(job);

值/行   1 2 3 4 5 6 7 8 9 10 11 12 13 14
---------------------------------------------------------------
ANALYST   0 0 0 0 0 0 0 1 0 0 0 0 1 0
CLERK 1 0 0 0 0 0 0 0 0 0 1 1 0 1
MANAGER   0 0 0 1 0 1 1 0 0 0 0 0 0 0
PRESIDENT       0 0 0 0 0 0 0 0 1 0 0 0 0 0
SALESMAN    0 1 1 0 1 0 0 0 0 0 0 0 0 0

SQL>select count(*) from emp1 where job = 'CLERK' or job = 'MANAGER';

值/行       1 2 3 4 5 6 7 8 9 10 11 12 13 14
-----------------------------------------------------------------------------
CLERK 1 0 0 0 0 0 0 0 0 0 1 1 0 1
MANAGER 0 0 0 1 0 1 1 0 0 0 0 0 0 0
-----------------------------------------------------------------------------
or的结果 1 0 0 1 0 1 1 0 0    0 1 1 0 1

常用的B树索引类型：

唯一或非唯一索引（Unique or non_unique): 唯一索引指键值不重复。
SQL> create unique index empno_idx on emp1(empno);
或
SQL> create index empno_idx on emp1(empno);

组合索引(Composite)：绑定了两个或更多列的索引。
SQL> create index job_deptno_idx on emp1(job,deptno);

反向键索引(Reverse)：将字节倒置后组织键值。当使用序列产生主键索引时，可以防止叶节点出现热块现象（考点）。
SQL> create index mgr_idx on emp1(mgr) reverse;

函数索引(Function base)：以索引列值的函数值为键值去组织索引
SQL> create index fun_idx on emp1(lower(ename));

压缩（Compress)：重复键值只存储一次，就是说重复的键值在叶块中就存一次，后跟所有与之匹配的rowid字符串。
SQL> create index comp_idx on emp1(sal) compress;

升序或降序（Ascending or descending)：叶节点中的键值排列默认是升序的。
SQL> create index deptno_job_idx on emp1(deptno desc, job asc);

可以更改索引属性：
alter index xxx ....

索引相关的数据字典

USER_INDEXES //索引主要信息
USER_IND_CULUMNS //索引列的信息

ITPub博客

索引详解

最新文章