oracle索引类型及扫描方式大整理.docx
- 文档编号:11134573
- 上传时间:2023-02-25
- 格式:DOCX
- 页数:43
- 大小:125.57KB
oracle索引类型及扫描方式大整理.docx
《oracle索引类型及扫描方式大整理.docx》由会员分享,可在线阅读,更多相关《oracle索引类型及扫描方式大整理.docx(43页珍藏版)》请在冰豆网上搜索。
oracle索引类型及扫描方式大整理
Oracle索引
1.存储类型
索引在各种关系型数据库系统中都是举足轻重的组成部分,其对于提高检索数据的速度起至关重要的作用。
在Oracle中,索引基本分为以下几种:
B*Tree索引,反向索引,降序索引,位图索引,函数索引,interMedia全文索引等。
本文主要就前6种索引进行分析。
首先给出各种索引的简要解释:
b*treeindex:
几乎所有的关系型数据库中都有b*tree类型索引,也是被最多使用的。
其树结构与二叉树比较类似,根据rid快速定位所访问的行。
反向索引:
反转了b*tree索引码中的字节,是索引条目分配更均匀,多用于并行服务器环境下,用于减少索引叶的竞争。
降序索引:
8i中新出现的索引类型,针对逆向排序的查询。
位图索引:
使用位图来管理与数据行的对应关系,多用于OLAP系统。
函数索引:
这种索引中保存了数据列基于function返回的值,在select*fromtablewherefunction(column)=value这种类型的语句中起作用。
B*Tree索引
B*Tree索引是最常见的索引结构,默认建立的索引就是这种类型的索引。
B*Tree索引在检索高基数数据列(高基数数据列是指该列有很多不同的值)时提供了最好的性能。
当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。
但当检查的范围超过表的10%时就不能提高取回数据的性能。
B-Tree索引是基于二叉树的,由分支块(branchblock)和叶块(leafblock)组成。
在树结构中,位于最底层底块被称为叶块,包含每个被索引列的值和行所对应的rowid。
在叶节点的上面是分支块,用来导航结构,包含了索引列(关键字)范围和另一索引块的地址,如图26-1所示。
假设我们要找索引中值为80的行,从索引树的最上层入口开始,定位到大于等于50,然后往左找,找到第2个分支块,定位为75-100,最后再定位到叶块上,找到80所对应的rowid,然后根据rowid去读取数据块获取数据。
如果查询条件是范围选择的,比如wherecolumn>20andcolumn<80,那么会先定位到第一个包含20的叶块,然后横向查找其他的叶块,直到找到包含80的块为止,不用每次都从入口进去再重新定位。
反向索引
反向索引是B*Tree索引的一个分支,它的设计是为了运用在某些特定的环境下的。
Oracle推出它的主要目的就是为了降低在并行服务器(OracleParallelServer)环境下索引叶块的争用。
当B*Tree索引中有一列是由递增的序列号产生的话,那么这些索引信息基本上分布在同一个叶块,当用户修改或访问相似的列时,索引块很容易产生争用。
反向索引中的索引码将会被分布到各个索引块中,减少了争用。
反向索引反转了索引码中每列的字节,通过dump()函数我们可以清楚得看见它做了什么。
举个例子:
1,2,3三个连续的数,用dump()函数看它们在Oracle内部的表示方法。
SQL>select'number',dump(1,16)fromdual
2 unionallselect'number',dump(2,16)fromdual
3 unionallselect'number',dump(3,16)fromdual;
'NUMBEDUMP(1,16)
-----------------------
numberTyp=2Len=2:
c1,2
(1)
numberTyp=2Len=2:
c1,3
(2)
numberTyp=2Len=2:
c1,4(3)
再对比一下反向以后的情况:
SQL>select'number',dump(reverse
(1),16)fromdual
2 unionallselect'number',dump(reverse
(2),16)fromdual
3 unionallselect'number',dump(reverse(3),16)fromdual;
'NUMBEDUMP(REVERSE
(1),1
-----------------------
numberTyp=2Len=2:
2,c1
(1)
numberTyp=2Len=2:
3,c1
(2)
numberTyp=2Len=2:
4,c1(3)
我们发现索引码的结构整个颠倒过来了,这样1,2,3个索引码基本上不会出现在同一个叶块里,所以减少了争用。
不过反向索引又一个缺点就是不能在所有使用常规索引的地方使用。
在范围搜索中其不能被使用,例如,wherecolumn>value,因为在索引的叶块中索引码没有分类,所以不能通过搜索相邻叶块完成区域扫描。
函数索引
基于函数的索引也是8i以来的新产物,它有索引计算列的能力,它易于使用并且提供计算好的值,在不修改应用程序的逻辑上提高了查询性能。
使用基于函数的索引有几个先决条件:
(1)必须拥有QUERYREWRITE(本模式下)或GLOBALQUERYREWRITE(其他模式下)权限。
(2)必须使用基于成本的优化器,基于规则的优化器将被忽略。
(3)必须设置以下两个系统参数:
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
可以通过altersystemset,altersessionset在系统级或线程级设置,也可以通过在init.ora添加实现。
这里举一个基于函数的索引的例子:
SQL>createindextest.ind_funontest.testindex(upper(a));
索引已创建。
SQL>insertintotestindexvalues('a',2);
已创建1行。
SQL>commit;
提交完成。
SQL>select/*+RULE*/* FROMtest.testindexwhereupper(a)='A';
A B
------------
a 2
ExecutionPlan
----------------------------------------------------------
0 SELECTSTATEMENTOptimizer=HINT:
RULE
1 0 TABLEACCESS(FULL)OF'TESTINDEX'
(优化器选择了全表扫描)
--------------------------------------------------------------------
SQL>select* FROMtest.testindexwhereupper(a)='A';
A B
------------
a 2
ExecutionPlan
----------------------------------------------------------
0 SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=1Bytes=5)
1 0 TABLEACCESS(BYINDEXROWID)OF'TESTINDEX'(Cost=2Card=
1Bytes=5)
2 1 INDEX(RANGESCAN)OF'IND_FUN'(NON-UNIQUE)(Cost=1Car
d=1)(使用了ind_fun索引)
降序索引
降序索引是8i里面新出现的一种索引,是B*Tree的另一个衍生物,它的变化就是列在索引中的储存方式从升序变成了降序,在某些场合下降序索引将会起作用。
举个例子,我们来查询一张表并进行排序:
SQL>select*fromtestwhereabetween1and100orderbyadesc,basc;
已选择100行。
ExecutionPlan
----------------------------------------------------------
0 SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=100Bytes=400)
1 0 SORT(ORDERBY)(Cost=2Card=100Bytes=400)
2 1INDEX(RANGESCAN)OF'IND_BT'(NON-UNIQUE)(Cost=2Card=100Bytes=400)
这里优化器首先选择了一个索引范围扫描,然后还有一个排序的步骤。
如果使用了降序索引,排序的过程会被取消。
SQL>createindextest.ind_descontest.testrev(adesc,basc);
索引已创建。
SQL>analyzeindextest.ind_desccomputestatistics;
索引已分析
再来看下执行路径:
SQL>select*fromtestwhereabetween1and100orderbyadesc,basc;
已选择100行。
ExecutionPlan(SQL执行计划,稍后会讲解如何使用)。
----------------------------------------------------------
0 SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=100Bytes=400)
1 0INDEX(RANGESCAN)OF'IND_DESC'(NON-UNIQUE)(Cost=2Card=100Bytes=400)
我们看到排序过程消失了,这是因为创建降序索引时Oracle已经把数据都按降序排好了。
另外一个需要注意的地方是要设置init.ora里面的compatible参数为8.1.0或以上,否则创建时desc关键字将被忽略。
位图索引
位图索引主要用于决策支持系统或静态数据,不支持行级锁定。
位图索引最好用于低cardinality列(即列的唯一值除以行数为一个很小的值,接近零),例如又一个“性别”列,列值有“Male”,“Female”,“Null”等3种,但一共有300万条记录,那么3/3000000约等于0,这种情况下最适合用位图索引。
位图索引可以是简单的(单列)也可以是连接的(多列),但在实践中绝大多数是简单的。
在这些列上多位图索引可以与AND或OR操作符结合使用。
位图索引使用位图作为键值,对于表中的每一数据行位图包含了TRUE
(1)、FALSE(0)、或NULL值。
位图索引的位图存放在B-Tree结构的页节点中。
B-Tree结构使查找位图非常方便和快速。
另外,位图以一种压缩格式存放,因此占用的磁盘空间比B-Tree索引要小得多。
位图索引的格式如表26-1所示。
表26-1 位图索引的格式
行
值12345678910
Male1000000011
Female0111001100
Null0000110000
如果搜索wheregender=’Male’,要统计性别是”Male”的列行数的话,Oracle很快就能从位图中找到共3行即第1,9,10行是符合条件的;如果要搜索wheregender=’Male’orgender=’Female’的列的行数的话,也很容易从位图中找到共8行即1,2,3,4,7,8,9,10行是符合条件的。
如果要搜索表的值的话,那么Oracle会用内部的转换函数将位图中的相关信息转换成rowid来访问数据块。
2.索引扫描方式
这里介绍CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时,才进行全Oracle索引扫描,而且此时查询出的数据都必须从索引中可以直接得到。
学习Oracle时,你可能会遇到Oracle索引扫描问题,这里将介绍Oracle索引扫描问题的解决方法,在这里拿出来和大家分享一下。
根据索引的类型与where限制条件的不同,有4种类型的Oracle索引扫描:
◆索引唯一扫描(indexuniquescan)
◆索引范围扫描(indexrangescan)
◆索引全扫描(indexfullscan)
◆索引快速扫描(indexfastfullscan)
(1)索引唯一扫描(indexuniquescan)
通过唯一索引查找一个数值经常返回单个ROWID。
如果该唯一索引有多个列组成(即组合索引),则至少要有组合索引的引导列参与到该查询中,如创建一个索引:
createindexidx_testonemp(ename,deptno,loc)。
则selectenamefromempwhereename=‘JACK’anddeptno=‘DEV’语句可以使用该索引。
如果该语句只返回一行,则存取方法称为索引唯一扫描。
而selectenamefromempwheredeptno=‘DEV’语句则不会使用该索引,因为where子句种没有引导列。
如果存在UNIQUE或PRIMARYKEY约束(它保证了语句只存取单行)的话,Oracle经常实现唯一性扫描。
使用唯一性约束的例子:
(2)索引范围扫描(indexrangescan)
使用一个索引存取多行数据,同上面一样,如果索引是组合索引,如
(1)所示,而且selectenamefromempwhereename=‘JACK’anddeptno=‘DEV’语句返回多行数据,虽然该语句还是使用该组合索引进行查询,可此时的存取方法称为索引范围扫描。
在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(如>、<、<>、>=、<=、between)
使用索引范围扫描的例子:
在非唯一索引上,谓词col=5可能返回多行数据,所以在非唯一索引上都使用索引范围扫描。
使用indexrangscan的3种情况:
(a)在唯一索引列上使用了range操作符(><<>>=<=between)
(b)在组合索引上,只使用部分列进行查询,导致查询出多行
(c)对非唯一索引列上进行的任何查询。
(3)索引全扫描(indexfullscan)
与全表扫描对应,也有相应的全Oracle索引扫描。
在某些情况下,可能进行全Oracle索引扫描而不是范围扫描,需要注意的是全Oracle索引扫描只在CBO模式下才有效。
CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时,才进行全Oracle索引扫描,而且此时查询出的数据都必须从索引中可以直接得到。
全Oracle索引扫描的例子:
(4)索引快速扫描(indexfastfullscan)
扫描索引中的所有的数据块,与indexfullscan很类似,但是一个显著的区别就是它不对查询出的数据进行排序,即数据不是以排序顺序被返回。
在这种存取方法中,可以使用多块读功能,也可以使用并行读入,以便获得最大吞吐量与缩短执行时间。
索引快速扫描的例子:
3.索引散记
3.1优化器模式
ORACLE的优化器共有3种:
a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性)
为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须定期更新统计信息,以保证数据库中的对象统计信息(object statistics)的准确性.
如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器。
3.2访问Table的方式
ORACLE 采用两种访问表中记录的方式:
a. 全表扫描
全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。
b. 索引扫描
你可以采用基于ROWID的访问方式情况,提高访问表的效率, ROWID包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.
其中ORACLE对索引又有两种访问模式.
a)索引唯一扫描 ( INDEX UNIQUE SCAN)
大多数情况下, 优化器通过WHERE子句访问INDEX.
例如:
表LOADING有两个索引 :
建立在LOADING列上的唯一性索引LOADING_PK和建立在MANAGER列上的非唯一性索引IDX_MANAGER.
SELECT loading
FROM LOADING
WHERE LOADING = ‘ROSE HILL’;
在内部 , 上述SQL将被分成两步执行, 首先 , LOADING_PK 索引将通过索引唯一扫描的方式被访问 , 获得相对应的ROWID, 通过ROWID访问表的方式执行下一步检索.
如果被检索返回的列包括在INDEX列中,ORACLE将不执行第二步的处理(通过ROWID访问表). 因为检索数据保存在索引中, 单单访问索引就可以完全满足查询结果.
下面SQL只需要INDEX UNIQUE SCAN 操作.
SELECT LOADING
FROM LOADING
WHERE LOADING = ‘ROSE HILL’;
b)索引范围查询(INDEX RANGE SCAN)
适用于两种情况:
1. 基于一个范围的检索
2. 基于非唯一性索引的检索
例1:
SELECT LOADING
FROM LOADING
WHERE LOADING LIKE ‘M%’;
WHERE子句条件包括一系列值, ORACLE将通过索引范围查询的方式查询LODGING_PK . 由于索引范围查询将返回一组值, 它的效率就要比索引唯一扫描
低一些.
例2:
SELECT LOADING
FROM LOADING
WHERE MANAGER = ‘BILL GATES’;
这个SQL的执行分两步, IDX_MANAGER的索引范围查询(得到所有符合条件记录的ROWID) 和下一步同过ROWID访问表得到LOADING列的值. 由于IDX_MANAGER是一个非唯一性的索引,数据库不能对它执行索引唯一扫描.
由于SQL返回LOADING列,而它并不存在于IDX_MANAGER索引中, 所以在索引范围查询后会执行一个通过ROWID访问表的操作.
WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.
SELECT LOADING
FROM LOADING
WHERE MANAGER LIKE ‘%HANMAN’;
在这种情况下,ORACLE将使用全表扫描.
3.3SQL调优的本质就是调整执行计划。
在好多情况下,oracle自动选择的执行计划并不是最优的,这时需要我们人工去干预。
(什么是执行计划?
)
对SQL调优基本步骤:
a)捕获SQL语句
b)产生SQL语句的执行计划;
c)验证统计信息(SQL语句涉及到的表格是否做过分析),表格信息(结果集的记录数,索引),字段上面数据分布特点
d)通过手工收集到的信息,形成自己理想的执行计划。
e)如果做过分析,则重新分析相关表格或者做柱状图分析。
f)如果没有做过分析,则通过尝试不同的Hint,从而获得合适的执行计划。
g)当我们正常无法调优到位时,可以打开10053事件打开优化器的跟踪,看看Oracle如何选择的.
alter session set events='10053 trace name context forever,level 2';
3.4如何捕获SQL语句
捕获SQL语句的方法有如下几种:
1.SQL TRACE或10046跟踪某个模块。
2.PERFSTAT性能统计包,使用方法见附录二。
3.V$SQL,V$SESSION_WAIT,V$SQL_TEXT
3.5如何查看执行计划
查看SQL语句的执行计划有以下几种:
1.Set autotrace on(set autotrace traceonly exp)
2.Explain plan for …..
@?
/rdbms/admin/utlxpls.sql
3.V$SQL_PLAN视图
column operation format a16
column "Query Plan" format a60
column options format a15
column object_name format a20
column id format 99
select id,lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' '
||decode(id,0,'Cost = '||position) "Query Plan"
from (select *
from v$sql_plan
where address='&a') sql_plan
start with id = 0
connect by prior id = parent_id
/
4.第三方工具,如pl/sql developer,TOAD
3.6SQL语句主要的连接方法
a)Nested-loop join
适合于小表(几千条,几万条记录)与大表做联接
在联接列上有索引。
分内表和外表(驱动表),靠近from子句的是内表。
从效率上讲,小表应该作外表,大表应该作内表,即大表查询时走索引。
COST= Access cost of A(驱动表) + (access cost of B * number of rows from A)
成本计算方法:
设小表100行,大表100000行。
两表均有索引:
如果小表在内,大
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 索引 类型 扫描 方式 整理