Oracle 索引基本原理

一.索引基本概念
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列上有索引,也会进行全表扫描

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:http://www.heiqu.com/54a351932523bfb76c2e30d845e73b6b.html