OracleBtree位图全文索引三大索引性能比较及优缺点汇总分析Word文件下载.docx
《OracleBtree位图全文索引三大索引性能比较及优缺点汇总分析Word文件下载.docx》由会员分享，可在线阅读，更多相关《OracleBtree位图全文索引三大索引性能比较及优缺点汇总分析Word文件下载.docx（12页珍藏版）》请在冰豆网上搜索。
altersystemflushbuffer_cache;
清空数据库缓冲区，都是为了实验需要
创建leo_t1
leo_t2表
leo_t1表的object_id列的数据是没有重复值的，我们抽取了10行数据就可以看出来了。
LS@LEO>
createtableleo_t1asselectobject_id,object_namefromdba_objects;
selectcount（*）fromleo_t1;
COUNT（*）
----------
9872
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树索引的影响
createtableleo_t2asselectmod（object_id,2）object_ID,object_namefromdba_objects;
selectcount（*）fromleo_t2;
9873
select*fromleo_t2whererownum<
0ICOL$
0I_USER1
0CON$
1UNDO$
1C_COBJ#
1I_OBJ#
1PROXY_ROLE_DATA$
1I_IND1
1I_CDEF2
0I_PROXY_ROLE_DATA$_1
createindexleo_t1_indexonleo_t1（object_id）;
创建B-tree索引，说明默认创建的都是B-tree索引
Indexcreated.
createindexleo_t2_indexonleo_t2（object_ID）;
创建B-tree索引
让我们看一下leo_t1与leo_t2的重复情况
selectcount（distinct（object_id））fromleo_t1;
让我们看一下leo_t1与leo_t2的重复情况，leo_t1没有重复值，leo_t2有很多
COUNT（DISTINCT（OBJECT_ID））
--------------------------
9872
selectcount（distinct（object_ID））fromleo_t2;
2
收集2个表统计信息
executedbms_stats.gather_table_stats（ownname=>
'
LS'
tabname=>
LEO_T1'
method_opt=>
forallindexedcolumnssize2'
cascade=>
TRUE）;
LEO_T2'
参数详解：
method_opt=>
size_clause=integer整型，范围1~254，使用柱状图[histogramanalyze]分析列数据的分布情况
cascade=>
TRUE
收集表的统计信息的同时收集B-tree索引的统计信息
显示执行计划和统计信息+设置autotrace简介
序号
命令
解释
1
SETAUTOTRACEOFF
此为默认值，即关闭Autotrace
2
SETAUTOTRACEONEXPLAIN
只显示执行计划
3
SETAUTOTRACEONSTATISTICS
只显示执行的统计信息
4
SETAUTOTRACEON
包含2,3两项内容
5
SETAUTOTRACETRACEONLY
与ON相似，但不显示语句的执行结果
结果键值少的情况
setautotracetraceexpstat;
（SETAUTOTRACEOFF关闭执行计划和统计信息）
select*fromleo_t1whereobject_id=1;
norowsselected
ExecutionPlan执行计划
----------------------------------------------------------
Planhashvalue:
3712193284
--------------------------------------------------------------------------------------------
|Id
|Operation
|Name
|Rows
|Bytes|Cost（%CPU）|Time
|
|
0|SELECTSTATEMENT
1|
21|
（0）|00:
00:
01|
TABLEACCESSBYINDEXROWID|LEO_T1
|
|*
2|
INDEXRANGESCAN索引扫描
|LEO_T1_INDEX|
PredicateInformation（identifiedbyoperationid）:
---------------------------------------------------
2-access（"
OBJECT_ID"
=1）
Statistics
统计信息
0
recursivecalls
dbblockgets
2
consistentgets
我们知道leo_t1表的object_id没有重复值，因此使用B-tree索引扫描只有2次一致性读
physicalreads
redosize
339
bytessentviaSQL*Nettoclient
370
bytesreceivedviaSQL*Netfromclient
1
SQL*Netroundtripsto/fromclient
sorts（memory）
sorts（disk）
rowsprocessed
结果键值多的情况
select*fromleo_t2whereobject_ID=1;
（select/*+full（leo_t2）*/*
fromleo_t2whereobject_ID=1;
hint方式强制全表扫描）
4943rowsselected.
ExecutionPlan
执行计划
3657048469
----------------------------------------------------------------------------
|Name
4943|98860|
12
TABLEACCESSFULL|LEO_T2|
01|sql结果是4943row，那么全表扫描也是4943row
1-filter（"
=1）
366
导致有366次一致性读
154465
4000
331
4943
大家肯定会疑惑，为什么要用全表扫描而不用B-tree索引呢，这是因为oracle基于成本优化器CBO认为使用全表扫描要比使用B-tree索引性能更好更快，由于我们结果重复率很高，导致有366次一致性读，从cup使用率12%上看也说明了B-tree索引不适合键值重复率较高的列
我们在看一下强制使用B-tree索引时，效率是不是没有全表扫描高呢？
select/*+index（leo_t2leo_t2_index）*/*fromleo_t2whereobject_ID=1;
hint方式强制索引扫描
4943rowsselected.
321706586
46
TABLEACCESSBYINDEXROWID|LEO_T2
INDEXRANGESCAN
|LEO_T2_INDEX|
4943|
10
704
使用B-tree索引704次一致性读>
全表扫描366次一致性读，而且cpu使用率也非常高，显然效果没有全表扫描高
171858
小结：
从以上的测试我们可以了解到，B-tree索引在什么情况下使用跟键值重复率高低有很大关系的，之间没有一个明确的分水岭，只能多测试分析执行计划后来决定。
位图索引
Bitmapindex
列的基数很少，可枚举，重复值很多，数据不会被经常更新
一个键值对应很多行（rowid），格式：
键值
start_rowid
end_rowid
位图
OLAP例如报表类数据库重复率高的数据特定类型的查询例如count、or、and等逻辑操作因为只需要进行位运算即可得到我们需要的结果
不适合重复率低的字段，还有经常DML操作（insert，update，delete），因为位图索引的锁代价极高，修改一个位图索引段影响整个位图段，例如修改
一个键值，会影响同键值的多行，所以对于OLTP系统位图索引基本上是不适用的
位图索引和B-tree索引的性能比较
setpagesize100;
设置页大小
利用dba_objects数据字典创建一个15万行的表
createtableleo_bm_t1asselect*fromdba_objects;
Tablecreated.
insertintoleo_bm_t1select*fromleo_bm_t1;
翻倍插入
9876rowscreated.
/
19752rowscreated.
39504rowscreated.
79008rowscreated.
158016rowscreated.
因object_type字段重复值较高，顾在此字段上创建bitmap索引
createbitmapindexleo_bm_t1_indexonleo_bm_t1（object_type）;
创建一个和leo_bm_t1表结构一模一样的表leo_bm_t2，并在object_type列上创建一个B-tree索引（15万行记录）
createtableleo_bm_t2asselect*fromleo_bm_t1;
createindexleo_bm_t2_bt_indexonleo_bm_t2（object_type）;
对比位图索引和B-tree索引所占空间大小,很明显位图要远远小于B-tree索引所占用的空间，节约空间特性也是我们选择位图的理由之一
selectsegment_name,bytesfromuser_segmentswheresegment_type='
INDEX'
;
SEGMENT_NAME
BYTES
-------------------------------------------------------------------------------------------
LEO_BM_T1_INDEX
327680（327K）
LEO_BM_T2_BT_INDEX
7340032（7M）
显示执行计划和统计信息
在创建有位图索引的表上做count操作对比执行计划
selectcount（*）fromleo_bm_t1whereobject_type='
TABLE'
3251686305
-----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
升级会员 