Oracle中的B-树索引和位图索引

      学习过程中对Oracle索引相关的知识做了一个整理，另外做了两个简单的实验测试B-树索引对执行计划的影响和位图索引的锁机制。

索引的作用：
      如果一个堆组织表没有索引，数据库必须执行全表扫描（扫描HWM下所有的块）。索引只影响执行速度。如果表上存在过多的索引，由于数据库需要维护索引，将会降低DML性能。

何时使用索引：
      1、要索引的列经常被查询，并只返回表中的行的总数的一小部分。
      2、列或列集上存在引用完整性约束（强烈建议对该列或列集建立索引）。如果建立索引将可以避免更新父表主键、合并父表、从父表删除行时可能引起的全表锁定。
      3、要在表上设置唯一键约束，并且您想手动指定索引和所有索引选项。

Oracle会在下面两种情况下自动创建索引：
      1、对于表的主键。
      2、对于表中具有唯一性约束的列。

可见索引与不可见索引：
      可见索引是指会被oracle自动维护，并且影响SQL的执行计划。
      不可见索引会被自动维护，但是执行SQL时，将不会考虑。不可见索引在测试索引效果时非常有用。

创建索引：
      CREATE INDEX index_name ON table_name (column_name)；

复合索引：
      在多个列上创建的索引即复合索引。
      复合索引的键应该按照SQL查询条件中的列的顺序来定义。只有查询包含索引的前导列（前面一部分列）或全部列时，才会使用复合索引。

非唯一索引：
      一个索引键值对应多行称为非唯一索引，非唯一索引中，值相同的记录按照rowid升序进行排序。

索引的分类：
      B-树索引是一个树结构，分支块用于查找，叶块用于存储值。分支块和叶块都包含指针，而所有叶块形成一个双向链表。
叶块包含键值和rowid，其中rowid用来定位行。

Oracle如何使用索引：
      索引中通过N个I/O就能找到要查找的值（N为索引高度）的rowid。
      另外如果SQL语句只要查找索引包含的列，则不需要再访问表，否则ORACLE将交替进行索引块读与数据块读。

快速完全索引扫描：
      当索引包含了查询所需的所有列，且索引键中至少一列具有NOT NULL约束时（否则列都为空的行不在索引中，从而必须读数据块），快速完全索引扫描可以替代全表扫描。
      SELECT name, department
      FROM employees;
      该查询不包含order by子句，并且name, department包含在索引中，此时会使用快速完全索引扫描。

索引范围扫描：
      SELECT name
      FROM employees
      WHERE name LIKE  ‘A%’;
      该查询如果对name列进行了索引，数据库可以使用范围扫描。
      索引范围扫描通过索引叶块的链表指针向前推进，获得范围内的所有数据（rowid）。

唯一索引扫描：
      一个索引键对应0个或者1个rowid的情况，找到一个记录就停止处理。

索引跳跃扫描：
      如果符合索引前导列中有少量不同值，非前导列中有大量不同值，并在SQL WHERE子句中未指定组合索引的前导列时候，数据库可能使用索引跳跃扫描。此时数据库根据前导列的不同值的个数在逻辑上将索引拆分为多个子索引。
      下面的查询：
      SELECT * FROM employees WHERE email=’[e-mail]’;
      如果表employees中对gender和email定义了复合索引，gender只有两个可能值F和M，而查询的WHERE子句并不包含索引前导列gender，此时数据库可以在逻辑上将索引拆分为只包含gender为F的索引和gender为M的索引，然后先搜索F的字索引，再搜索M的子索引。相当于数据库将SELECT子句拆分为两个，并执行UNION ALL操作：
      SELECT * FROM employees WHERE gender=’F’  AND  email=’[e-mail]’
      UNION ALL
      SELECT * FROM employees WHERE gender=’M’  AND  email=’[e-mail]’;

索引聚簇因子：
      被索引的行存储的越有序则聚簇因子越低，反之则越高。
      索引是通过叶块进行有序查找，如果数据实际存储非常无序，索引条目就指向随机表块，通过索引的查找就可能需要随机反复读取相同数据块，这样就显著增加了I/O。
      反之，反复重读相同数据块的几率就会降低。
      索引聚簇因子将决定数据库是否使用索引。
      在视图DBA_INDEXES中查询聚簇因子：
      SELECT INDEX_NAME, CLUSTERING_FACTOR
      FROM DBA_INDEXES
      WHERE INDEX_NAME =’’;

反向键索引：
      如果数据库在同一时间需要维护同一索引叶块，就会引发索引叶块争用。
      此时使用反向键索引。如果索引键值为20。那么在反向键索引中，该键值为02。这样如果对键值为20和21的行修改，则维护的索引叶块为02所在的叶块和12所在的叶块，这样两个叶块为同一块的几率将降低（相对的20和21在同一叶块的几率相当高）。但是同时反向键索引不适合范围扫描。

降序索引：
      在CREATE INDEX语句中指定DESC关键字，让索引行按从大到小的顺序排列（默认为从小到大）。当要查询按一些列升序而另一些列降序排列时，降序索引非常有用。此时按一些列升序，一些列降序建立复合索引可以使用此索引检索数据并避免额外的排序步骤。
         

位图索引
      将要锁定位图的操作，将会锁定该位图指向的所有记录。 所以位图索引不适合OLTP应用。
      例如：插入一个键值为N的记录（已在该列上创建位图索引），将会锁定索引列为N的整个位图，则所有该列值为N的记录都会被锁定。

何时使用位图索引：
      数据仓库或只读表，并且索引列的基数很低。
      对键值位图进行逻辑运算即可以获得结果。
      与B-树索引不同，位图索引可以包含空值键。

位图联接索引：
      CREATE BITMAP INDEX employees_bm_idx
      ON employees (jobs.job_title)
      FROM employees, jobs
      WHERE employees.job_id = jobs.job_id;

位图存储结构：
Oracle数据库使用一个B-树索引结构来为每个索引键存储位图，单个位图存储在叶块中。



实验一：测试B-树索引对执行计划的影响。
1、     建立一个100W行的表。
其中单数行的gender值为M，双数行的gender值为F。
SQL> create table tt(id number,gender varchar2(8));

Table created.

SQL> declare
  2  i number;
  3  g varchar2(8);
  4  begin
  5  for i in 1..1000000 loop
  6  if mod(i,2)=0 then
  7  g:='F';
  8  else
  9  g:='M';
10  end if;
11  insert into tt values(i,g);
12  end loop;
13  commit;
14  end;
15  /

PL/SQL procedure successfully completed.

SQL> select count(*) from tt;
  COUNT(*)
----------
   1000000

SQL> commit;
Commit complete.

2、     建立索引之前查看执行计划。
SQL> explain plan for select * from tt where id=500000;

Explained.

SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 264906180

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    18 |   342 |   456   (2)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| TT   |    18 |   342 |   456   (2)| 00:00:06 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("ID"=500000)

Note
-----
   - dynamic sampling used for this statement (level=2)

17 rows selected.

可以看出oracle执行全表扫描，CPU为456，时间为6s。

3、     建立索引之后查看执行计划。

SQL> CREATE INDEX TT_ID_IDX ON TT(id);

Index created.

SQL>  explain plan for select * from tt where id=500000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1275420880

--------------------------------------------------------------------------------
---------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
ime     |

--------------------------------------------------------------------------------
---------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    19 |     4   (0)| 0
0:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TT        |     1 |    19 |     4   (0)| 0
0:00:01 |

|*  2 |   INDEX RANGE SCAN          | TT_ID_IDX |     1 |       |     3   (0)| 0
0:00:01 |

--------------------------------------------------------------------------------
---------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=500000)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

可以看到查询改为走索引了（索引范围扫描），CPU降到4，时间降到1s。



实验二：测试位图索引的锁机制(对某一位图中某个记录的修改会锁定该位图指向的所有记录)。
1、     建立位图索引。
SQL> CREATE BITMAP INDEX TT_GENDER_IDX ON TT(GENDER);

Index created.


2、     插入一行。
SQL> insert into tt values(1000001,'F');

1 row created.

开启另一个session
查询锁定信息：
SQL> select * from v$lock where type='TX';

ADDR             KADDR                   SID TY        ID1        ID2      LMODE
---------------- ---------------- ---------- -- ---------- ---------- ----------
   REQUEST      CTIME      BLOCK
---------- ---------- ----------
000000036CE00AA8 000000036CE00B20        226 TX     327707       8124          6
         0          7          0
可以看到session226锁定了一个对象，锁类型为TX，锁定的对象的地址为000000036CE00AA8

第二个session中试图更新id=500000的记录（该记录gender=F）

3、     插入记录
SQL> insert into tt values(1000002,'F');
等待始终无结果，说明事务被阻塞。

第一个会话中rollback
则第二个会话中1 row created.

注意：此时第二个会话update或是delete  gender=F的列可以成功。此时插入gender=M的行也不会有问题。唯独插入gender=F的行会被阻塞。似乎和文档描述有出入，待进一步研究。此次测试版本为11GR2，与网友测试结果(其他oracle版本，F位图对应的所有行都已锁定)有所不同。

总结：在这个1000000行的表中，由于位图索引的存在，第一个会话只是插入了一个记录（gender=F），第二个会话就不能再插入记录（gender=F）。