没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
索引
一、概述
索引是一种可以选择创建的数据库对象,它主要用于提高查询性能。数据库索引
把用户感兴趣的列值连同其行标识符(ROWID)存储在一起。ROWID 包含了存储列值
的表行在磁盘上的物理位置,索引中记录了 rowid,因此 oracle 就可以根据索引中
的 rowid 来判断记录是否是在同一个 block 中,通过下面语句找到记录对应的 block
号。
Select dbms_rowid.rowid_block_number(rowid) from dual;
Oracle 可以通过最少量的磁盘读取,有效地检索表中的数据。如果没有可用的索
引,Oracle 就必须读取表中的每一行,才能确定该行是否包含所需的信息。
索引是有代价的,索引会消耗磁盘空间和系统资源。在列值被修改的同时也必须
更新相应的索引。因此,索引使用了存储空间、I/O、CPU 和内存资源。创建十分糟
糕的索引,会浪费磁盘空间,并且会过度消耗系统资源,也会导致数据库的性能下
降。所以,不能为所有的表和列组合创建索引。一般来说,增加索引会降低 INSERT
语句的性能,因为需要同时对表和索引进行操作。索引列上的 UPDATE 操作将会比没
有加索引慢很多,因为数据库必须管理对表和索引的改动。此外,大量行的 DELETE
操作将会由于表中存在索引而变慢。所以,需要平衡索引带来的查询性能的提升和
对数据修改性能的影响。
所以,必须了解索引,知道有什么类型的索引可用,应该选择哪些表和列的组合
来创建索引。
当访问表的数据时,Oracle 提供了两种方式:从表中读取所有行(即全表扫描),
或者通过 ROWID 一次读取一行。当访问大数据量表中的少量行时,可以使用索引。
如果只访问大数据量表中 5%的行,可以使用索引标识需要读取的数据块,这样花费
的 I/O 较少。如果没有使用索引,就要读取表中所有的数据块。
索引对性能改进的程度取决于数据的选择性以及数据在表的数据块中的分布方
式。
如果数据选择性很好(例如编号),Oracle 将能够快速查询索引,找到匹配索引值
的 ROWID,从而快速查到表中相应的少量数据块。如果数据选择性不好(例如地区名),
那么对一个值索引可能返回许多 ROWID,导致从表中查询许多不连续的数据块。
如果数据选择性很好,但是相关的行在表中的物理存储位置并不互相靠近,如果
匹配索引值的数据分散在表的多个数据块中,就必须从表中把每个数据块都选出来
以得到需要的查询结果。在一些情况下,会发现当数据分散在表的多个数据块中时,
最好不使用索引,而是选择全表扫描。执行全表扫描时,Oracle 使用多块读取以快
速扫描表。
通过 dba_indexes 视图查询表的所有索引,user_indexes 视图查询当前方案
(schema)的索引,all_indexes 视图查看能够访问的所有表的索引。
create index emp_id2 on emp (sal);
当执行这些命令时,数据库将在 emp 表上创建索引。索引将包含 emp 表中的特定
值以及匹配这些值的行的 ROWID。如果需要查找 sal 值为 1000 的 emp 表中的记录,
优化器就会使用 emp_id2 索引查找该值,并找到相关的 ROWID,接着使用该 ROWID 在
表中查找对应的行。
select table_name, index_name from user_indexes where table_name = 'EMP' ;
查询 user_ind_columns、dba_ind_columns 和 all_ind_columns 视图查询表中被
索引的列的信息。
column index_name format a12
column column_name format a8
column table_name format a8
select table_name, index_name, column_name, column_position
from user_ind_columns order by table_name, index_name, column_position;
二、限制索引使用的情况
在很多情况下,即使创建了索引,也会导致索引不能使用,下面看一下限制使用
索引的几种情况:
1、使用不等于运算符(<>、!=)
当在 WHERE 子句中使用不等于运算符(<>、!=)时,其中被用到的列上的索引将
无法使用。
alter session set container=pdb1;
SYS@orcl>select index_name,index_type,TABLE_NAME from dba_indexes where
table_name='EMP';
INDEX_NAME INDEX_TYPE TABLE_NAME
---------- ------------- ---------
EMP_PK NORMAL EMP
SYS@orcl>select index_name,table_name,column_name from dba_ind_columns
where index_name='EMP_PK';
INDEX_NAME TABLE_NAME COLUMN_NAME
--------- --------- -----------
EMP_PK EMP EMPNO
SYS@orcl>explain plan for select * from emp where empno<>10;
SYS@orcl> select * from table(dbms_xplan.display);
当分析表的时候,oracle 收集表中数据分布的相关统计信息,基于成本的优化器
决定在 where 子句中对一些值使用索引,而对其它的值不使用索引,因此,不是说
在一个列上建立了索引就一直会使用索引,根据不同值,优化器会确定是否使用索
引。
2、使用 is null 或 is not null
在 where 子句中使用 is null 或 is not null 同样会限制索引的使用,如果被索
引的列在某些行中存在 null 值,在索引列中就不会有相应的条目。位图索引对于 null
列会进行记录,因此位图索引对于 null 搜索通常较为快速。
在 emp 表的 sal 列创建 B 树索引
SYS@orcl> create index idx_sal_emp on emp(sal);
SYS@orcl>select index_name,table_name,column_name from dba_ind_columns
where table_name='EMP';
在查询中使用 is null 来查询,此时使用了全表扫描,因此建议对列加上 not null
或 default,防止 null 值的出现而导致该列上的索引不能用。
SYS@orcl> explain plan for select empno,ename,deptno from emp where sal
is null;
SYS@orcl> select * from table(dbms_xplan.display);
在创建表时对列指定 NOT NULL 或 DEFAULT 属性,可以帮助避免可能出现的性能
问题。
创建表为 deptno 列提供了一个默认值。如果在 INSERT 操作时该列没有指定值,
就会使用默认值。如果指定了默认值,并且确实需要 NULL 值,就需要在该列中
插入 NULL。
create table employee1 (empl_id number(8) not null, first_name
varchar2(20) not null,last_name varchar2(20) not null, deptno number(4)
default 10);
insert into employee1(empl_id, first_name, last_name) values (8100,
'REGINA', 'NIEMIEC');
select * from employee1;
insert into employee1 values (8200, 'RICH', 'NIEMIEC', NULL);
select * from employee1;
commit;
3、使用函数
如果不使用基于函数的索引,那么在 SQL 语句的 where 子句中对存在索引的列使
用函数时,会使优化器忽略掉这些索引。一些最常见的函数,如 trunc、substr、
to_date、to_char 和 instr 等都可以改变列的值,因此,被引用的列上的索引将无
法使用。
SYS@orcl> create index sal_hire on emp(hiredate);
SYS@orcl> explain plan for select empno,ename,deptno from emp where
trunc(hiredate)='03-dec-81';
SYS@orcl> select * from table(dbms_xplan.display);
将上面的语句改成下面的样子,就可以通过索引进行查找:
explain plan for select empno,ename,deptno
from emp
where hiredate>'03-dec-81' and hiredate< (TO_DATE('03-dec-81') +
0.99999);
select * from table(dbms_xplan.display);
解决办法:
1)使用基于函数的索引。
2)灵活书写 SQL,避免在索引列上使用 SQL 函数。
4、比较不匹配的数据类型
比较不匹配的数据类型,oracle 不会对那些不兼容的数据类型报错,oracle 会
做隐式数据转换。例如,oracle 可以隐式地转换 varchar2 类型的列中的数据去匹配
数值类型数据。
create table test0(id VARCHAR2(5),name VARCHAR2(8));
create index ind_test0 on test0(id);
insert into test0 values('4','a');
commit;
不使用索引
explain plan for select * from test0 where id=4;
select * from table(dbms_xplan.display());
使用索引
explain plan for select * from test0 where id='4';
select * from table(dbms_xplan.display());
三、类型的索引
Oracle 提供了丰富的索引类型,正确地使用索引可以产生良好的性能。Oracle
索引的类型如下:
索引类型 用 途
B 树索引
默认的索引类型,平衡树索引,适用于高基数(不
同值程度高)的列。除非有特殊原因,需要使用不
同的索引类型或功能,否则用正常的 B 树索引即可
索引组织表
当主键包含大多数的列值时很有效率。数据存储在
一个类似 B 树索引的结构表中
唯一索引
B 树索引的一种形式,用于强制执行列值的唯一
性。经常与主键和唯一键约束一起使用,但也可以
独立于约束而创建
反向键索引
B 树索引的一种形式,在索引有许多顺序插入的情
况下,用于平衡 I/O
键压缩索引
适用于前导列经常重复的组合索引,压缩叶块条
目。此功能适用于 B 树索引或索引组织表(IOT)
索引
剩余60页未读,继续阅读
资源评论
CodeGolang
- 粉丝: 55
- 资源: 1088
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功