Oracle Btree位图全文索引三大索引性能比较及优缺点汇总.docx

1、Oracle Btree位图全文索引三大索引性能比较及优缺点汇总引言:大家都知道“效率”是数据库中非常重要的一个指标,如何提高效率大家可能都会想起索引,但索引又这么多种,什么场合应该使用什么索引呢?哪种索引可以提高我们的效率,哪种索引可以让我们的效率大大降低(有时还不如全表扫描性能好下面要讲的“索引”如何成为我们的利器而不是灾难!多说一点,由于不同索引的存储结构不同,所以应用在不同组织结构的数据上,本篇文章重点就是:理解不同的技术都适合在什么地方应用!B-Tree索引场合:非常适合数据重复度低的字段例如身份证号码手机号码 QQ号等字段,常用于主键唯一约束,一般在在线交易的项目中用到的多些。原理

2、:一个键值对应一行(rowid格式:【索引头|键值|rowid】优点:当没有索引的时候,oracle只能全表扫描where qq=40354446 这个条件那么这样是灰常灰常耗时的,当数据量很大的时候简直会让人崩溃,那么有个B-tree索引我们就像翻书目录一样,直接定位rowid 立刻就找到了我们想要的数据,实质减少了I/O操作就提高速度,它有一个显著特点查询性能与表中数据量无关,例如查2万行的数据用了3 consistent get,当查询1200万行的数据时才用了4 consistent gets。当我们的字段中使用了主键or唯一约束时,不用想直接可以用B-tree索引缺点:不适合键值重复

3、率较高的字段上使用,例如第一章1-500page 第二章501-1000page实验:alter system flush shared_pool; 清空共享池alter system flush buffer_cache; 清空数据库缓冲区,都是为了实验需要创建leo_t1 leo_t2 表leo_t1 表的object_id列的数据是没有重复值的,我们抽取了10行数据就可以看出来了。LSLEO create table leo_t1 as select object_id,object_name from dba_objects;LSLEO select count(* from leo_

4、t1;COUNT(*-9872LSLEO select * from leo_t1 where rownum create table leo_t2 as select mod(object_id,2 object_ID ,object_name from dba_objects;LSLEO select count(* from leo_t2;COUNT(*-9873LSLEO select * from leo_t2 where rownum create index leo_t1_index on leo_t1(object_id; 创建B-tree索引,说明默认创建的都是B-tree索

5、引Index created.LSLEO create index leo_t2_index on leo_t2(object_ID; 创建B-tree索引Index created.让我们看一下leo_t1与leo_t2的重复情况LSLEO select count(distinct(object_id from leo_t1; 让我们看一下leo_t1与leo_t2的重复情况,leo_t1没有重复值,leo_t2有很多COUNT(DISTINCT(OBJECT_ID-9872LSLEO select count(distinct(object_ID from leo_t2;COUNT(DI

6、STINCT(OBJECT_ID-2收集2个表统计信息LSLEO execute dbms_stats.gather_table_stats(ownname=LS,tabname=LEO_T1,method_opt=for all indexed columns size 2,cascade=TRUE;LSLEO execute dbms_stats.gather_table_stats(ownname=LS,tabname=LEO_T2,method_opt=for all indexed columns size 2,cascade=TRUE;参数详解:method_opt=for all

7、 indexed columns size 2 size_clause=integer 整型,范围1254 ,使用柱状图 histogram analyze 分析列数据的分布情况cascade=TRUE 收集表的统计信息的同时收集B-tree索引的统计信息显示执行计划和统计信息+设置autotrace简介序号命令解释1 SET AUTOTRACE OFF 此为默认值,即关闭Autotrace2 SET AUTOTRACE ON EXPLAIN 只显示执行计划3 SET AUTOTRACE ON STATISTICS 只显示执行的统计信息4 SET AUTOTRACE ON 包含2,3两项内容5

8、 SET AUTOTRACE TRACEONLY 与ON相似,但不显示语句的执行结果结果键值少的情况set autotrace trace exp stat; (SET AUTOTRACE OFF 关闭执行计划和统计信息LSLEO select * from leo_t1 where object_id=1;no rows selectedExecution Plan 执行计划-Plan hash value: 3712193284-| Id | Operation | Name | Rows | Bytes | Cost (%CPU| Time |-| 0 | SELECT STATEMEN

9、T | | 1 | 21 | 2 (0| 00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID| LEO_T1 | 1 | 21 | 2 (0| 00:00:01 |* 2 | INDEX RANGE SCAN索引扫描 | LEO_T1_INDEX | 1 | | 1 (0| 00:00:01 |- Predicate Information (identified by operation id:-2 - access(OBJECT_ID=1Statistics 统计信息-0 recursive calls0 db block gets2 consistent

10、 gets 我们知道leo_t1表的object_id没有重复值,因此使用B-tree索引扫描只有2次一致性读0 physical reads0 redo size339 bytes sent via SQL*Net to client370 bytes received via SQL*Net from client1 SQL*Net roundtrips to/from client0 sorts (memory0 sorts (disk0 rows processed结果键值多的情况LSLEO select * from leo_t2 where object_ID=1; (select

11、 /*+full(leo_t2 */ * from leo_t2 where object_ID=1;hint方式强制全表扫描4943 rows selected.Execution Plan 执行计划-Plan hash value: 3657048469-| Id | Operation | Name | Rows | Bytes | Cost (%CPU| Time |-| 0 | SELECT STATEMENT | | 4943 | 98860 | 12 (0| 00:00:01 |* 1 | TABLE ACCESS FULL| LEO_T2 | 4943 | 98860 | 12 (0| 00:00:01 | sql结果是4943row,那么全表扫描也是4943row-Predicate Information (identified by operation id:-1 - filter(OBJECT_ID=1Statistics 统计信息-