索引-oracle

本章节大部分内容都是从书上以及网上摘录。


带着一下几个问题往下看：
1、oracle中有哪些索引类型

2、是不是只要加了索引，索引就一定会被使用呢？

一、oracle中索引类型

oracle 索引类型
B树索引
位图索引
hash索引
索引组织表索引 (IOT：index organized table)
反转键(reverse key)索引
基于函数的索引(本地和全局索引)
分区索引
位图链接索引

如果按照列的个数可以分为 单列索引、复合索引

下面选几个介绍一下

1、B树索引

在oracle中是一个通用索引，创建索引时他就是默认的索引类型，即我们创建的normal索引就是这种数据结构。B树索引可以是一个单列索引也可以是复合索引，b树索引最多包含32列。

一个B树索引只有一个根节点，它实际就是位于树的最顶端的分支节点。
可以用下图一来描述B树索引的结构。其中，B表示分支节点，而L表示叶子节点。

对于分支节点块（包括根节点块）来说，其所包含的索引条目都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以叫做每条记录）都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值；第二个字段为四个字节，表示所链接的索引块的地址，该地址指向下面一个索引块。在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。比如从上图一可以看到，对于根节点块来说，包含三条记录，分别为（0 B1）、（500 B2）、（1000 B3），它们指向三个分支节点块。其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。而B1、B2和B3则表示所指向的三个分支节点块的地址。

对于叶子节点块来说，其所包含的索引条目与分支节点一样，都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以叫做每条记录）也具有两个字段。第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所对应的记录行的ROWID，该ROWID是记录行在表里的物理地址。如果索引是创建在非分区表上或者索引是分区表上的本地索引的话，则该ROWID占用6个字节；如果索引是创建在分区表上的全局索引的话，则该ROWID占用10个字节。

小技巧：索引列的值都存储在索引中。因此，可以建立一个复合索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了 I/O 量。

2、bitmap索引

位图（bitmap）索引是另外一种索引类型，它的组织形式与B树索引相同，也是一棵平衡树。与B树索引的区别在于叶子节点里存放索引条目的方式不同。从前面我们知道，B树索引的叶子节点里，对于表里的每个数据行，如果被索引列的值不为空的，则会为该记录行在叶子节点里维护一个对应的索引条目。
而位图索引则不是这样，其叶子节点里存放的索引条目如下图所示。

假设某个表T里所有的记录在列C1上只具有三个值：01、02和03。在表T的C1列上创建位图索引以后，则叶子节点的内容如图9-14所示。可以看到，位图索引只有三个索引条目，也就是每个C1列的值对应一个索引条目。位图索引条目上还包含表里第一条记录所对应的ROWID以及最后一条记录所对应的ROWID。索引条目的最后一部分则是由多个bit位所组成的bitmap，每个bit位就对应一条记录。

当发出where c1='01'这样的SQL语句时，Oracle会去搜索01所在的索引条目，然后扫描该索引条目中的bitmap里所有的bit位。第一个bit位为1，则说明第一条记录上的C1值为01，于是返回第一条记录所在的ROWID（根据该索引条目里记录的start ROWID加上行号得到该记录所在的ROWID）。第二个bit位为0，则说明第二条记录上的C1值不为01，依此类推。另外，如果索引列为空，也会在位图索引里记录，也就是将对应的bit位设置为0即可。
       如果索引列上不同值的个数比较少的时候，比如对于性别列（男或女）等，则使用位图索引会比较好，因为它对空间的占用非常少（因为都是用bit位来表示表里的数据行），从而在扫描索引的时候，扫描的索引块的个数也比较少。可以试想一下，如果在列的不同值非常多的列上，比如主键列上，创建位图索引，则产生的索引条目就等于表里记录的条数，同时每个索引条目里的bitmap里，只有一个1，其它都是0。这样还不如B树索引的效率高。

如果被索引的列经常被更新的话，则不适合使用位图索引。因为当更新位图所在的列时，由于要在不同的索引条目之间修改bit位，比如将第一条记录从01变为02，则必须将01所在的索引条目的第一个bit位改为0，再将02所在的索引条目的第一个bit位改为1。因此，在更新索引条目的过程中，会锁定位图索引里多个索引条目。也就是同时只能有一个用户能够更新表T，从而降低了并发性。

例如，对于性别列，其索引只能有两个唯一值，那么用这个索引对表的任何搜索有 可能都返回一半的记录。其次，这些索引是为数据仓库而设计的，所以其假定条件是数据不会发生很大的改变。这些索引不能用来满足事务数据库或更新频繁的数据库。应该说，对位图索引的表进行更新根本没有一点效率。

3、函数索引

执行一个像下面的指令：WHERE UPPER(first_name) = ‘JOHN’。这是不会使用first_name字段上的索引。为什么?因为Oracle不得不将UPPER函数用在该索引所有(ALL)的值上，所以oracle优化器判定还不如做一次全表扫描。所以，很多时候Oracle创建基于函数的索引就是为了这个目的。

a，基于函数的索引是常规的B树索引。 
b，该索引存放的数据是由表中的数据应用函数后得到的，而不是直接存放表中的数据本身。 创建基于函数的索引时，oracle会首先对包含索引列的函数值或者表达式值进行求值，然后对求职后的结果进行排序，最后存储到索引中。
c，如果查询条件包含与索引相同的函数，就可以使用基于函数的索引，提高查询速度。
d，如果用户在自己模式中创建基于函数的索引，那么必须具有query rewrite系统权限； 
如果用户想要在其他模式中创建基于函数的索引，需要create any index和global query rewrite权限 

创建函数索引：create index 索引名 on table(函数名(列名));

4、ORACLE使用索引时一些辅助视图

常用的oracle索引视图
较为重要的oracle索引视图如下：
dba_indexes
user_indexes
dba_ind_columns
user_indexes
dba_expressions
user_expressions

其中：
dba_indexes与user_indexes视图，主要涵盖了索引的参数、状态以及关联的表信息，但不包含具体的列信息。
dba_ind_columns与user_ind_columns视图，主要涉及具体的索引列的信息。
dba_expressions与user_expressions视图，主要针对函数索引，可以查看具体的函数信息。

例：可以查询某个表的索引信息
select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS 
from user_ind_columns a,user_indexes b 
where a.INDEX_NAME=b.INDEX_NAME and a.table_name='TEST01';

二、限制索引，有时候即便添加了索引，oracle优化器也会不予使用

1.1 oracle优化器在后台工作，选择并使用可能最有效的数据检索方法。例如在许多情况下不需要指定where子句,oracle也会使用索引：
1.1.1、查询索引列的MIN或MAX值，oracle将从索引中而不是从数据表中检索该值，因为索引中已经包含了索引列的值，select max(stu_no) from student2 。
1.1.2、对索引列执行count函数，oracle也是直接使用索引而不是数据列，select count(stu_no) from student2 该语句虽然没有添加where stu_no='SN12'，但是也会直接查询索引。
所以，因为主键默认系统添加了索引的，故而 select count(*) from student2 会自动根据主键索引查询。


1.2 当然还有可能会出现限制索引的情况(没有经验的开发人员经常犯的错误之一)，一些where字句的逻辑可能阻止oracle使用索引的情况：
1.2.1、使用不等于运算符(<>,!=)
索引只能用于查找表中已有的数据。当在where字句中使用不等于运算符时，都将无法使用所引用的列的索引。
例如：select ename, sal from emp where deptno!=20;
即使emp表中的deptno列有索引，上述语句仍会执行一次全表扫描(因为大多数记录都可以被检索到)。
注意：当分析表时，oracle会收集表中数据分布的相关统计信息，通过使用这种分析，基于成本的优化器就可以决定在where子句中对一些值使用索引，而对其他的值不使用索引。
技巧：可以通过使用 create index命令的 compute statistics子句，可以在一个步骤中创建索引并分析它们。


1.2.2、使用is null 或 is not null
在where字句中使用 is null 或 is not null 同样会限制索引的使用，因为 null 值并没有被定义。数据库中没有值等于 null 值；甚至 null 也不等于 null。
如果被索引的列在某些行中存在 null 值，在索引中就不会有相应的条目(例外：位图索引，这就是位图索引对于 null 搜索通常比较快速的原因)。
select deptno, ename from emp where sal is null;
上述语句执行将造成全表扫描，即使sal列示索引列。


1.2.3、使用函数
如果不使用函数索引，那么在where字句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。一些常见的函数，如trunc,substr,to_date,to_char,instr等。
select epname, deptno from emp where trunc(hiredate) = '01-MAY-01';
上述语句将会执行一次全表扫描，即使hiredate列上存在索引(只要它不是函数索引)。

其实这个是可以避免的，只需要我们灵活的换一种写法：
select epname, deptno from emp where hiredate > to_date('01-MAY-01') and hiredate < (todate('01-MAY-01')+0.999);


1.2.4、比较不匹配的数据类型
比较不匹配的数据类型也是比较难于发现的性能问题之一。 oracle 并不会对那些不匹配的数据报错-事实正好相反；例如，oracle可以隐式的把 varchar2 列的数据转换成想要
被比较的数值型数据类型。看如下例子，其中 account_number 就是一个varchar2类型。
select bank_name, address, city, state from banks where account_number=990345;
oracle 可以自动把where子句变成：to_number(account_number)=990345;
这样就限制了索引的使用。这个查询的执行计划 EXPLAIN PLAN 仅显示通过“全表扫描”访问这个表（对编程人员来说通常都很迷惑）。
所以，对于以上语句可以修改为：select bank_name, address, city, state from banks where account_nuber='990345';
或者把 account_number 字段变为数字类型，当然前提是account_number字段不存在前置是0的数据。

1.2.5、使用like进行模糊查询
语句1 ：select epname, deptno from emp where sal like 'a%'; --使用索引
语句2 ：select epname, deptno from emp where sal like '%a'; --不会使用索引
语句3 ：select epname, deptno from emp where sal like '%a%'; --不会使用索引

可以看到只有 '...%'一种情况才会使用到索引，所以为了查询效率有以下方式：

     1。尽量不要使用 like '%%'
     2。对于 like '%' (不以 % 开头)，Oracle可以应用 colunm上的index
     3。对于 like '%…' 的 (不以 % 结尾)，可以利用reverse + function index 的形式，变化成 like '%'
     4。非用like'%%'不可时，使用Oracle内部函数：INSTR()解决。

1.2.6、等于和范围索引不会被合并使用
select epname from emp where sal='abc' and deptno>20;
sal 和 deptno 都是非唯一索引，这种条件下 oracle 不会合并索引，它只会使用第一个索引。

三、复合索引的使用情况

CREATE TABLE student2 (
user_id NUMBER(12),
stu_no VARCHAR2(32),
user_name VARCHAR2(32),
sex NUMBER(2),
birthday DATE,
primary key (user_id)
);


create INDEX INDEX_STUDENT2_NO_NAME ON STUDENT2(STU_NO, USER_NAME, SEX);


INSERT INTO STUDENT2(USER_ID, STU_NO, USER_NAME, SEX, BIRTHDAY) VALUES (1, 'SN100', '常明', 1, SYSDATE);
INSERT INTO STUDENT2(USER_ID, STU_NO, USER_NAME, SEX, BIRTHDAY) VALUES (2, 'SN101', '花花', 2, SYSDATE);
INSERT INTO STUDENT2(USER_ID, STU_NO, USER_NAME, SEX, BIRTHDAY) VALUES (3, 'SN102', '小王', 1, SYSDATE);
INSERT INTO STUDENT2(USER_ID, STU_NO, USER_NAME, SEX, BIRTHDAY) VALUES (4, 'SN103', '李雷', 1, SYSDATE);
INSERT INTO STUDENT2(USER_ID, STU_NO, USER_NAME, SEX, BIRTHDAY) VALUES (5, 'SN104', '张三', 2, SYSDATE);


在where子句中使用AND谓词那么会使用该复合索引；如果单独使用deptno列也会使用该复合索引；
但是，如果使用OR谓词引用复合索引中的两个或多个列，或者单独引用USER_NAME、sex列，那么不会使用该复合索引。
会引用举例如下：
select * from student2 where stu_no='SN100';
select * from student2 where stu_no='SN100' and USER_NAME='常明';
select * from student2 where stu_no='SN100' and sex=1;
select * from student2 where stu_no='SN100' and USER_NAME='常明' and sex=1;
select * from student2 where sex=1 and stu_no='SN100';
select * from student2 where USER_NAME='常明' and stu_no='SN100';
select * from student2 where stu_no='SN100' and birthday=sysdate;
select * from student2 where birthday=sysdate and stu_no='SN100';--只要符合 索引的引用规则，条件中含有非索引列，索引也是会起作用的。
select * from student2 where stu_no='SN100' or stu_no='SN101';--这里虽然是 or 谓词，但是都是同一个列stu_no，所以是会用到索引的。
select stu_no from student2 where stu_no<'SN100' or stu_no>'SN101';--只查询了一个列，而且这个列是索引列，优化器判定使用索引 I/O更少，效率更高，所以是会用到索引的；如果查询列改为 * 就不会使用到索引了。




不会引用举例如下：
select * from student2 where USER_NAME='常明' and sex=1;
select * from student2 where USER_NAME='常明';
select * from student2 where sex=1;
select * from student2 where stu_no='SN100' or USER_NAME='常明';--带 or 谓词的复合索引多个列不会引用
select * from student2 where stu_no>'SN100' or stu_no<'SN100';--。
select * from student2 where stu_no!='SN100'; -- 使用 != 号不会使用索引




再看一个非常有趣而且非常有用的事情：
EXPLAIN PLAN
SET STATEMENT_ID = '77' FOR
select * from student2 where stu_no='SN100';
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


EXPLAIN PLAN
SET STATEMENT_ID = '78' FOR
select USER_NAME from student2 where stu_no='SN100';
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


EXPLAIN PLAN
SET STATEMENT_ID = '79' FOR
select STU_NO, USER_NAME, SEX from student2 where stu_no='SN100';
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


看下这三句sql，他们的区别 第一句查询的是 * 即表中所有字段，第二句查询的是复合索引中的一个字段 USER_NAME，第三句查询的正好和复合索引字段对应，
查看下执行计划会发现，第一句sql 查询了索引以后还会去查数据表，但是第二、第三句sql 直接查询了索引后没有再去查询数据表!
所以得出：
索引列的值都存储在索引中！！！因此，可以建立一个复合索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了 I/O 量。

我们加复合索引的时候应注意：一般来说，复合索引的第一列应该是最有可能在where子句中使用的列，并且也是复合索引中最具选择性的列。


下面转载一段别的文章的观点，写的非常一针见血：
都是为了优化器
如果你曾经广泛接触过MySQL和其他的数据库，你会发现甲骨文虽然是全球领先的数据库供应商，但它们的数据库对于用户来说用起来其实并不 是很方便。提到优化器这个问题可能有点离题了，不过Oracle数据库最基本的食料就是优化器了，这的确是种挺特别的调料，而且变得越来越美味了。市面上 有很多以Oracle基于代价的优化器(Cost Based Optimizer，CBO)为主题内容的书籍，专门介绍分析表和索引的技巧和策略。
对于数据库，除了需要一直更新你的统计信息之外，你可能还需要不断测试新的查询。使用解析计划机制，并进行优化以便减少总I/O量以及排序和合并数据的计算量，只有这样你才能获得更好的性能表现。
总结
虽然Oracle数据库的索引世界有点吓人，不过实际上你平常经常使用的索引就只有那么一些。而且，不管唱反调的人怎样诋毁，Oracle 的优化器都已经设计相当出色;总体而言，Oracle很擅长于让你的数据库运行地更有效率。虽然这并不意味着你不需要对自己的SQL进行调优，不过，如果 你一直保持着最新的统计信息，并让Oracle为你整理出你所需要的最小数据集的话，它能够以极快的速度满足你的需要。