Oracle Btree位图全文索引三大索引性能比较及优缺点汇总.docx
《Oracle Btree位图全文索引三大索引性能比较及优缺点汇总.docx》由会员分享，可在线阅读，更多相关《Oracle Btree位图全文索引三大索引性能比较及优缺点汇总.docx（17页珍藏版）》请在冰豆网上搜索。
OracleBtree位图全文索引三大索引性能比较及优缺点汇总
引言:
大家都知道“效率”是数据库中非常重要的一个指标,如何提高效率大家可能都会想起索引,但索引又这么多种,什么场合应该使用什么索引呢?
哪种索引可以提高我们的效率,哪种索引可以让我们的效率大大降低（有时还不如全表扫描性能好下面要讲的“索引”如何成为我们的利器而不是灾难!
多说一点,由于不同索引的存储结构不同,所以应用在不同组织结构的数据上,本篇文章重点就是:
理解不同的技术都适合在什么地方应用!
B-Tree索引
场合:
非常适合数据重复度低的字段例如身份证号码手机号码QQ号等字段,常用于主键唯一约束,一般在在线交易的项目中用到的多些。
原理:
一个键值对应一行（rowid格式:
【索引头|键值|rowid】
优点:
当没有索引的时候,oracle只能全表扫描whereqq=40354446这个条件那么这样是灰常灰常耗时的,当数据量很大的时候简直会让人崩溃,那么有个B-tree索引我们就像翻书目录一样,直接定位rowid立刻就找到了我们想要的数据,实质减少了I/O操作就提高速度,它有一个显著特点查询性能与表中数据量无关,例如查2万行的数据用了3consistentget,当查询1200万行的数据时才用了4consistentgets。
当我们的字段中使用了主键or唯一约束时,不用想直接可以用B-tree索引
缺点:
不适合键值重复率较高的字段上使用,例如第一章1-500page第二章501-1000page
实验:
altersystemflushshared_pool;清空共享池
altersystemflushbuffer_cache;清空数据库缓冲区,都是为了实验需要
创建leo_t1leo_t2表
leo_t1表的object_id列的数据是没有重复值的,我们抽取了10行数据就可以看出来了。
LS@LEO>createtableleo_t1asselectobject_id,object_namefromdba_objects;
LS@LEO>selectcount（*fromleo_t1;
COUNT（*
----------
9872
LS@LEO>select*fromleo_t1whererownum<=10;
OBJECT_IDOBJECT_NAME
---------------------
20ICOL$
44I_USER1
28CON$
15UNDO$
29C_COBJ#
3I_OBJ#
25PROXY_ROLE_DATA$
39I_IND1
51I_CDEF2
26I_PROXY_ROLE_DATA$_1
leo_t2表的object_id列我们是做了取余操作,值就只有0,1两种,因此重复率较高,如此设置为了说明重复率对B树索引的影响
LS@LEO>createtableleo_t2asselectmod（object_id,2object_ID,object_namefromdba_objects;
LS@LEO>selectcount（*fromleo_t2;
COUNT（*
----------
9873
LS@LEO>select*fromleo_t2whererownum<=10;
OBJECT_IDOBJECT_NAME
---------------------
0ICOL$
0I_USER1
0CON$
1UNDO$
1C_COBJ#
1I_OBJ#
1PROXY_ROLE_DATA$
1I_IND1
1I_CDEF2
0I_PROXY_ROLE_DATA$_1
LS@LEO>createindexleo_t1_indexonleo_t1（object_id;创建B-tree索引,说明默认创建的都是B-tree索引
Indexcreated.
LS@LEO>createindexleo_t2_indexonleo_t2（object_ID;创建B-tree索引
Indexcreated.
让我们看一下leo_t1与leo_t2的重复情况
LS@LEO>selectcount（distinct（object_idfromleo_t1;让我们看一下leo_t1与leo_t2的重
复情况,leo_t1没有重复值,leo_t2有很多
COUNT（DISTINCT（OBJECT_ID
--------------------------
9872
LS@LEO>selectcount（distinct（object_IDfromleo_t2;
COUNT（DISTINCT（OBJECT_ID
--------------------------
2
收集2个表统计信息
LS@LEO>executedbms_stats.gather_table_stats（ownname=>'LS',tabname=>'LEO_T1',method_opt=>'forallindexedcolumnssize2',cascade=>TRUE;
LS@LEO>executedbms_stats.gather_table_stats（ownname=>'LS',tabname=>'LEO_T2',method_opt=>'forallindexedcolumnssize2',cascade=>TRUE;
参数详解:
method_opt=>'forallindexedcolumnssize2'size_clause=integer整型,范围1~254,使用柱状图[histogramanalyze]分析列数据的分布情况
cascade=>TRUE收集表的统计信息的同时收集B-tree索引的统计信息
显示执行计划和统计信息+设置autotrace简介
序号命令解释
1SETAUTOTRACEOFF此为默认值,即关闭Autotrace
2SETAUTOTRACEONEXPLAIN只显示执行计划
3SETAUTOTRACEONSTATISTICS只显示执行的统计信息
4SETAUTOTRACEON包含2,3两项内容
5SETAUTOTRACETRACEONLY与ON相似,但不显示语句的执行结果
结果键值少的情况
setautotracetraceexpstat;（SETAUTOTRACEOFF关闭执行计划和统计信息
LS@LEO>select*fromleo_t1whereobject_id=1;
norowsselected
ExecutionPlan执行计划
----------------------------------------------------------
Planhashvalue:
3712193284
--------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU|Time|
--------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|21|2（0|00:
00:
01|
|1|TABLEACCESSBYINDEXROWID|LEO_T1|1|21|2（0|00:
00:
01|
|*2|INDEXRANGESCAN索引扫描|LEO_T1_INDEX|1||1（0|00:
00:
01|
--------------------------------------------------------------------------------------------PredicateInformation（identifiedbyoperationid:
---------------------------------------------------
2-access（"OBJECT_ID"=1
Statistics统计信息
----------------------------------------------------------
0recursivecalls
0dbblockgets
2consistentgets我们知道leo_t1表的object_id没有重复值,因此使用B-tree索引扫描只有2次一致性读
0physicalreads
0redosize
339bytessentviaSQL*Nettoclient
370bytesreceivedviaSQL*Netfromclient
1SQL*Netroundtripsto/fromclient
0sorts（memory
0sorts（disk
0rowsprocessed
结果键值多的情况
LS@LEO>select*fromleo_t2whereobject_ID=1;（select/*+full（leo_t2*/*fromleo_t2whereobject_ID=1;hint方式强制全表扫描
4943rowsselected.
ExecutionPlan执行计划
----------------------------------------------------------
Planhashvalue:
3657048469
----------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU|Time|
----------------------------------------------------------------------------
|0|SELECTSTATEMENT||4943|98860|12（0|00:
00:
01|
|*1|TABLEACCESSFULL|LEO_T2|4943|98860|12（0|00:
00:
01|sql结果是4943row,那么全表扫描也是4943row
----------------------------------------------------------------------------
PredicateInformation（identifiedbyoperationid:
---------------------------------------------------
1-filter（"OBJECT_ID"=1
Statistics统计信息
--------------------------------
升级会员 