Oracle索引基本原理

Oracle提供了两种方式:从表中读取所有行(即全表扫描)，或者通过ROWID一次读取一行。如果只访问大数据量表中的5%的行，并且使用索

一.索引基本概念
Oracle提供了两种方式:从表中读取所有行(即全表扫描)，或者通过ROWID一次读取一行。

如果只访问大数据量表中的5%的行，并且使用索引标识需要读取的数据块，这样话费的I/O较少.索引对性能改进的程度：1.取决于数据的选择性 2.数据在表的数据块中的分布方式

当数据分散在表的多个数据块中时，最好是不使用索引，而是选择全表扫描。执行全表扫描时，oracle使用多块读取以快速扫描表，基于索引的读是单块读。因此在使用索引时的目标是减少完成查询所需的单块读的数量。

增强索引会降低insert语句的性能(因为需要同时对表和索引进行更新).大量行的delete操作将会由于表中存在索引而变慢

在表上加一个索引都会使该表上insert操作的执行时间变成原来的三倍，再加一个索引就会再慢一倍。
视图：
DBA_INDEXS
USER_INDEXS
ALL_INDEXS
显示表的索引

USER_IND_COLUMNS
DBA_IND_COLUMNS
ALL_IND_COLUMNS
显示所有被索引的列

二.不可视索引
每插入一条记录时，就会更新所有索引.oracle允许关闭索引(使其不可见)，但是索引上的维护工作还会继续。
alter index idx1 invisible
alter index idx2 visible
create index .. invisible

create index dept_inv_idx on dept_rich(deptno) invisible;
select count(*) from dept_rich where deptno=30;(索引不可见)

在执行计划里是看不到使用索引了
可以使用强制索引使用，通过USE_INVISIBLE_INDEXS提示，或者把初始化参数OPTIMIZER_USE_INVISIBLE_INDEXS设置成true。

select /*+ USE_INVISIBLE_INDEXS */ count(*) from dept_rich where deptno = 30;(通过提示强制使用)
执行计划可以看到使用索引了

在不将其变成不可见的前提下，也可以使用NO_INDEX提示来关闭一个索引.
select /*+ no_index(dept_rich dept_rich_inv_idx) */ count(*) from dept_rich where deptno = 30;(强制不使用带提示的索引)

会发现执行计划走的全表扫描

可以随时将这个索引设成不可见
alter index dept_rich_inv_idx invisible;

三.组合索引
当某个索引包含多个列时，我们称这个索引为"组合索引" 或 "复合索引".

引入的索引跳跃式扫描增加了优化器在使用组合索引时的选择，所以在选择索引中的列顺序时应该谨慎。
索引的第一列应该是最有可能在where子句中使用的列，并且也是索引中最具选择性的列。

create index emp_id1 on emp(empno，ename，deptno);

跳跃式扫描select job,empno from emp where ename='RICH'; 即使where子句中没有指定empno值，优化器也可能会选择使用该索引.也可能使用索引快速扫描或全表扫描。

如果在where子句中使用索引的第三列，也会产生相同的情况。
优化器可能选择索引跳跃式扫描，索引快速扫描或全表扫描。

最常见的索引扫描方式是唯一扫描和范围扫描。在唯一扫描中，数据库知道索引包含每一个值都是唯一的。在范围扫描中，数据库将根据查询条件从索引中返回多个符合条件的值。上面的都是范围扫描
使用create unique index命令可以创建唯一索引.

在创建主键约束或唯一性约束时，oracle将基于指定的列自动创建唯一索引(除非使用disable子句创建约束)。如果创建多列的主键，oracle将创建组合索引，其中的列的排列顺序和创建主键时指定的列的顺序一致。

四.索引抑制
在SQL中有很多陷阱会使一些索引无法使用
1.使用不等于运算符(<> , !=)
索引只能查找表中已存在的数据，每当在where子句中使用不等于运算符时，其中被用到的列上的索引都将无法使用。
例如：select cust_id，cust_name from customers where cust_rating <> 'aa';
oracle在分析表的同时收集表中数据分布的相关统计信息。通过这种方法，基于成本的优化器就可以决定在where子句中对一些值使用索引，而对其他的值不使用.
可以通过create index 命令的compute statistic子句，在创建索引的同时分析它们。

2.使用IS NULL或IS NOT NULL
当在where子句中使用IS NULL或IS NOT NULL的时候，因为NULL值并没有被定义(oracle不会在B树索引中对NULL值索引).所以索引的使用会被抑制。数据库中没有值等于NULL;甚至NULL也不等于NULL.
如果被索引的列在某些行中存在NULL值，在索引中就不会有相应的条目(除非使用位图索引，这是位图索引对于NULL搜索通常很快的原因)。
例如:select empno，deptno from emp where sal is null;
即使sal列上有索引，也会进行全表扫描

只有在表中每一行值都非NULL或是使用alter table命令的default子句时，才可以为列设置NOT NULL属性.
alter table emp modify (sal not null);

create table p(id int，nameid number(4) default 10);
insert into p values(1，NULL);
insert into p values(2,20);
显示:
ID NAMEID
1
2 20
创建表时对列指定NOT NULL或default 可以帮助避免出现的性能问题.

3.使用LIKE
条件中有LIKE关键字会使用索引。
通常有两种写法 LIKE '%some%' 或LIKE 'some%'
当%在前面的时候，索引不会被使用，但是当值在%前面时候，oracle可以使用索引

4.使用函数
除非使用基于函数的索引，否则在SQL语句的where子句中对存在的索引的列使用函数时，优化器会忽略索引.一般常见的索引如：TRUNC,SUBSTR,TO_DATE,TO_CHAR和INSTR等。
例如下面的就会使用全表扫描
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);
通过改变所比较的列上的值，而不用改变列本身，就可以启用索引，这样可避免全表扫描.

5.比较不匹配的数据类型
一种很难解决的性能问题是比较不匹配的数据类型。oracle不但不会对那些不兼容的数据类型报错，反而会做隐式数据转换。例如：oracle可以隐式得转换varchar2类型的列中的数据去匹配数值类型数据。
如果account_number列是varchar2数据类型，下面将进行全表扫描
select bank_name，，address，city，state，zip from banks where account_number=99999;
oracle会自动转换成 to_number(account_number) = 99999; 这样就抑制了索引的使用.

如果上面的语句加上单引号就会使用索引了
select bank_name，address，city，state，zip from banks where account_number='99999';