欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle 索引基本原理

程序员文章站 2022-03-21 12:12:06
...

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 通过改变所比较的列上的值,而不用改变列本身,就可以启用索引,这样可避免全表扫描.

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';