Oracle11gR2——索引

0、相关概念
rowid：
rowid为18位，指定了行的物理地址，其的格式如下：
数据对象编号_文件编号_块编号_行编号
OOOOOO_FFF_BBBBBB_RRR

是否对NULL值创建索引条目：
B-树索引不为NULL值创建索引条目，位图索引为NULL创建索引条目。

1、索引的类型及扫描方式

1.1 B-树索引

平衡树即B-树，是最常见的数据库索引类型。B-树索引结构如下图所示：



B-树索引有两种类型的块：用于查找的分支块和用于存储值的叶块。
B-树索引的所有叶块都处于相同的深度，并且以双向链表相连。
叶块存储被索引的数据值以及对应的rowid。
数据库扫描索引时，只需要n个I/O就能够获得需要的rowid，n为索引高度。如果SQL仅访问索引包含的列则不需要读取数据块，否则将根据rowids来查找表中的行。

1.2 索引扫描方式

1.2.1 完全索引扫描——INDEX FULL SCAN
如果SQL中的WHERE子句引用了一个索引列，那么可能使用完全索引扫描。
完全扫描可以消除排序，因为数据本身就是基于索引键排过序的。
假设department_id, last_name, salary为表employees上的一个复合索引键，那么下列sql语句

SELECT department_id, last_name, salary
FROM employees
WHERE salary > 5000
ORDER BY department_id, last_name;

这样数据库会执行索引全扫描，扫描的顺序可能如下所示：

50,Atkinson,2800,rowid
60,Austin,4800,rowid
70,Baer,10000,rowid
80,Abel,11000,rowid
80,Ande,6400,rowid
100,Austin,7200,rowid
......

注意：虽然sql语句中包含ORDER BY子句，但是其实并不需要排序，因为扫描的顺序本身就是有序的。
完全索引扫描相当于将全部索引叶块全部扫描了一遍。

完全索引扫描消除排序的例子：

SQL> set autotrace on
SQL> select department_id,last_name,salary from test.employees
  2  where salary>5000
  3  order by department_id,last_name;

DEPARTMENT_ID LAST_NAME                SALARY
------------- -------------------- ----------
           70 Bear                      10000
           80 Abel                      11000
           80 Ande                       6400
          100 Austin                     7200
执行计划
----------------------------------------------------------
Plan hash value: 3447538987
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     4 |   152 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |           |     4 |   152 |     4  (25)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES |     4 |   152 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
   2 - filter("SALARY">5000)
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        637  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          4  rows processed

此时未创建索引，执行计划为table access full，并且执行了排序。

创建复合索引：

SQL> create index idx_employees on test.employees(department_id,last_name,salary);

索引已创建。

SQL> select department_id,last_name,salary from test.employees where salary >5000 order by department_id,last_name;

DEPARTMENT_ID LAST_NAME                SALARY
------------- -------------------- ----------
           70 Bear                      10000
           80 Abel                      11000
           80 Ande                       6400
          100 Austin                     7200

执行计划
----------------------------------------------------------
Plan hash value: 2032394372
--------------------------------------------------------------------------------

| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time
 --------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |     4 |   152 |     1   (0)| 00:00:01
|*  1 |  INDEX FULL SCAN | IDX_EMPLOYEES |     4 |   152 |     1   (0)| 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SALARY">5000)
       filter("SALARY">5000)
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        637  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed
创建索引之后，执行计划为index full scan，并且未进行排序。

1.2.2 快速完全索引扫描

当索引本身包含了需要查询的列的时候，数据库仅访问索引，而不需要访问表。
对于下面的SQL

SELECT last_name,salary
FROM employees;

如果姓氏和工资是一个符合索引键，那么快速完全索引扫描只需读取索引条目。

1.2.3 索引范围扫描

在条件中指定了一个或多个索引前导列，且一个索引键可能对应多个值，此时可能使用索引范围扫描。

进行索引范围扫描时，数据库将在叶块之间前后移动（使用叶块之间的链表）。


1.2.4 唯一索引扫描

当在具有唯一约束或主键约束的列上创建索引时，WHERE子句以该列进行查询时，数据库执行唯一索引扫描。
数据库只需要执行n次I/O就能找到行的rowid，n为索引的高度。

1.2.5索引跳跃扫描

假设在customers表中有一列cust_gender，其值为M或F。假定在列cust_gender和cust_email上存在一个复合索引。则复合索引可能如下所示：

F,Wolf@company.com,rowid
F,Wolsey@company.com,rowid
F,Yang@company.com,rowid
M,Abbassi@company.com,rowid

SQL语句为

SELECT * FROM sh.customers WHERE cust_email='Abbey@company.com';

数据库在逻辑上将该索引拆分为一个具有F键的子索引和另一个具有M键的子索引。相当于下面的语句：


SELECT * FROM sh.customers WHERE cust_gender='F'
  AND cust_email = 'Abbey@company.com'
UNION ALL
SELECT * FROM sh.customers WHERE cust_gender='M'
  AND cust_email = 'Abbey@company.com'
  
1.3 索引的选择性、聚集因子与二元高度

1.3.1 选择性

user_indexes表中的distinct_keys的值与表总行数的比值，决定了索引的选择性。选择性越高，通过一个索引值找到的rowid就越少。主键或唯一约束的列上的索引，该值等于表的行数。


1.3.2 聚集因子


based on the values of the index.
If the value is near the number of blocks, then the
table is very well ordered. In this case, the index
entries in a single leaf block tend to point to rows in
the same data blocks.
If the value is near the number of rows, then the
table is very randomly ordered. In this case, it is
unlikely that index entries in the same leaf block
point to rows in the same data blocks.

该值越接近表的数据块数，则说明表越有序，一个索引叶块倾向于指向同一个表数据块中的行。如果该值接近于表的行数，则情况相反。

如果聚集因子较高，则在大型索引范围扫描过程中，数据库将执行相对较高数据的I/O。索引条目指向随机表块，因此数据库可能必须一遍又一遍地来回重读索引所指向的同一数据块。


如果聚集因子较低，则在大型索引范围扫描过程中数据库将执行相对较低数目的I/O。在一个范围内的索引键倾向于指向相同的数据块，因此该数据库不比来回重读相同的数据块。


1.3.3 二元高度

B-树的高度，0代表根即为叶。该值影响到索引的性能，重建索引可能会降低该值。

这些索引的相关信息决定了oracle数据库会不会使用索引。

一般来说，数据库中的数据块越大，索引的二元高度就越低。二元高度每增加一个级别，在DML操作期间就会增加额外的性能成本。

1.4 反向键索引

B-树索引中的键值，在物理上反转每个索引键的字节，即为反向键索引。例如索引键值为20，在普通B-树索引中被存储为C1 15,那么在反向键索引中被存储为15 C1。

在Oracle RAC环境中，如果每个实例都将它的键写入索引相同的叶块（例如键值递增的情况），会形成索引争用。此时使用反向键索引，将这些键值分布到不同的索引叶块，避免了索引争用。即均匀分布了I/O。

反向键索引的缺点：不能执行范围索引扫描。因为索引的值在字节上已被反转。

1.5 升序索引与降序索引

对数值，字符，日期创建的B-树索引键值默认是升序排列（较小值所在的叶块在树的左边）。在CREATE INDEX语句中指定DESC可以创建降序索引。

如果有一个复合索引，包括姓氏与部门两个字段，且姓氏升序，部门降序。那么一个要求以姓氏升序排列，部门降序排列的查询通过索引扫描将不需要额外的排序步骤。

1.6 键压缩

复合键索引中，假设第一个字段重复度较高，那么重复存储这些值将浪费空间，此时采取键压缩。

1.7 位图索引

1.7.1 单表的位图索引

例如在客户表中的性别字段上的位图索引如下图：



如果需要查询离婚的女性客户，那么通过位图的"逻辑和"操作即可得到结果，如下图所示：



1.7.2 位图联接索引

例如查询某一职位的雇员数：

SELECT COUNT(*)
FROM employees,jobs
WHERE employees.job_id = jobs.job_id
AND jobs.job_title = 'Accountant';

若要从索引本身检索数据，而不是从表中扫描，可以创建位图联接索引：

CREATE BITMAP INDEX employees_bm_idx
ON     employees (jobs.job_title)
FROM   employees, jobs
WHERE  employees.job_id = jobs.job_id;

位图索引的存储结构

Oracle使用B-树来存储位图索引，每一个叶块包含多个索引条目，每一个条目包含：键值、rowids范围的低值、rowids范围的高值以及该rowids范围内的位图。


例如上面的连接位图中，jobs.job_title是一个位图索引的键列，那么该索引的一个叶块可能包含下列索引条目：


Shipping Clerk,AAAPzRAAFAAAABSABQ,AAAPzRAAFAAAABSABZ,0010000100
Shipping Clerk,AAAPzRAAFAAAABSABa,AAAPzRAAFAAAABSABh,010010
Stock Clerk,AAAPzRAAFAAAABSAAa,AAAPzRAAFAAAABSAAc,1001001100
Stock Clerk,AAAPzRAAFAAAABSAAd,AAAPzRAAFAAAABSAAt,0101001001
Stock Clerk,AAAPzRAAFAAAABSAAu,AAAPzRAAFAAAABSABz,100001
......

如果一个会话修改了某一雇员的job_id，从Shipping Clerk改为Stock Clerk，那么索引需要更新两个条目，这时，直至UPDATE提交之前，Oracle数据库会锁定这两个条目所指向的所有行。

1.8 基于函数的索引

基于函数的索引计算函数或涉及一个或多个列的表达式的值，并将其存储在索引中。

仅当在WHERE子句中包含函数计算时，数据库才会使用基于函数的索引。

注意：为了维护基于函数的索引，执行INSERT与UPDATE语句时，数据库将进行额外的函数计算。

一个创建基于函数的索引的例子：

CREATE INDEX emp_total_sal_idx
ON employees (12 * salary * commission_pct, salary, commission_pct);

括号中包含三项，第一项为函数表达式，后面两项为列名。

又例如有下面查询：

SELECT *
FROM employees
WHERE UPPER(first_name) = 'AUDREY';

则可以创建基于函数的索引：

CREATE INDEX emp_fname_uppercase_idx
ON employees ( UPPER(first_name) );

1.9 应用程序域索引

Oracle支持使用专门的索引技术为各类复杂的数据类型自定义索引。

1.10 索引的存储

Oracle数据库将索引数据存储在一个索引段中，索引段的表空间是所有者的默认表空间。

如果在CREATE INDEX语句中为索引指定表空间，那么可以不备份改表空间，因为索引表空间可以重建。

2、索引抑制

一些SQL语句的写法会阻止Oracle使用索引，下面的情况中将不会使用索引（即使在列上创建索引）

2.1 使用不等于运算符（<>、!=）

下面的查询将不会使用索引

select cust_id, cust_name
from customers
where cust_rating<>'aa';

2.2 使用IS NULL或IS NOT NULL或IS

下面的查询将不会使用索引

select empno, ename, deptno
from emp
where sal is null;

2.3 使用LIKE

下面的查询不会使用索引

select empno, ename, hiredate
from scott.emp
where ename like '%BLAKE%';

而下面的查询会使用索引

select empno, ename, hiredate
from scott.emp
where ename like 'BLAKE%';

2.4 使用函数

当索引不是基于函数的索引时，下面的查询不会使用索引

select empno, ename, deptno
from emp
where trunc(hiredate)='01-MAY-01';

而下面的查询会使用索引

select empno, ename, deptno
from emp
where hiredate > '01-MAY-01'
and hiredate < (TO_DATE('01-MAY-01')+0.99999)

2.5 使用不匹配的数据类型

如果表banks上account_number列是varchar2类型，下面的查询将不使用索引

select * 
from banks
where account_number=990354;

而下面的查询会使用索引

select * 
from banks
where account_number='990354';

3、不可视索引

数据库不会使用不可视索引，除非为SQL指定提示，或者改变初始化参数。

使用下面语句创建（或修改为）不可视索引

CREATE INDEX...INVISIBLE;
ALTER INDEX idx1 INVISIBLE;
ALTER INDEX idx1 VISIBLE;

在11gR2中使用提示或修改初始化参数OPTIMIZER_USER_INVISIBLE_INDEXES=true，可以使不可视索引起效。

select /* USE_INVISIBLE_INDEXES*/ count(*)
from dept
whre deptno=30;

也可以通过/*+ no_index(table_name index_name)*/提示关闭查询中的索引。

USER_INDEXES中的visibility列显示了索引是否可见。

4、操作对索引的影响

4.1 插入操作对索引的影响

插入数据会在索引的叶块中插入条目，随着叶块变满，叶块会进行分裂。

如果插入的键值大于该叶块的所有键值，采用90-10分裂，相当于仅为新条目创建一个叶块。

否则采用50-50分裂，将键值较小的一半条目放到一个叶块，较大的一半条目放到另一叶块。

4.2 更新操作对索引的影响

更新索引键的值时，索引会执行一个删除一个插入操作，删除只是一个删除标记，只有在索引条目被删除而且块被清理后，空间才能被重用。

4.3 删除操作对索引的影响

删除表的行时，对应的索引条目会被标记为删除，在清理过程清理之前，空间不能被重用。

4.4 标记为删除的索引条目何时被清理

表中插入数据，即索引插入条目时，会迫使条目被清理，以重用空间。

如果表从不插入数据，此时索引块最终被延迟块清理功能清理。

4.5 数据块大小对索引的影响

分支块到跟块都有可能分裂，这种行为会导致索引的高度和分支的增加。理论上使用更大的数据块的表空间创建索引，可以减少分裂次数。

5、何时使用索引

Oracle会自动（强制）在主键上创建索引。

开发人员应当在外键上创建索引。

位图索引在更新记录时会锁定表中的一些行，因此不适合OLTP数据库，常用在数据仓库。

B-树索引同一键值对应的行数越少，一次SELECT取出的结果越少，越适合使用索引。