服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Oracle - Oracle中索引位图转换的优势

Oracle中索引位图转换的优势

2021-08-28 01:05数据和云张程 Oracle

索引位图转换是优化器对目标表上的一个或多个目标索引执行位图布尔运算。Oracle数据库里有一个映射函数(Mapping Function),它可以实现B树索引中ROWID和对应位图索引中的位图之间互相转换。目的是对相同ROWID做AND、OR等连接运算。

Oracle中索引位图转换的优势

一章 Oracle索引位图转换介绍

 1.1 索引位图转换

首先介绍一下索引位图转换概念:

索引位图转换是优化器对目标表上的一个或多个目标索引执行位图布尔运算。Oracle数据库里有一个映射函数(Mapping Function),它可以实现B树索引中ROWID和对应位图索引中的位图之间互相转换。目的是对相同ROWID做AND、OR等连接运算。

当执行计划中出现“BITMAP CONVERSION FROM/TO ROWIDS”、“BITMAP AND”,说明Oracle对应的索引将其中的ROWID转换成了位图,然后对转换后的位图执行了BITMAP AND(位图按位与)布尔运算。最后将布尔运算的结果再次用映射函数转换成了ROWID并回表得到最终的结果。

1.2 性能分析

根据我们以往的经验,用映射函数将ROWID转换成位图,这期间可能访问了多个索引,甚至一个索引会访问N多次。然后在执行位图布尔运算。最后再将运算结果转换为ROWID并回表,这个过程在实际生产环境中的执行效率往往是有问题的,我们可以通过隐藏参数_b_tree_bitmap_plans禁掉该过程中从ROWID到位图的转换。

但实际上当我们看到“BITMAP CONVERSION FROM/TO ROWIDS”的执行计划,一定代表着存在性能问题吗?

下面我用一个案例来说明:

创建测试表结构如下:

  1. DROP TABLE T1 PURGE; 
  2. CREATE TABLE T1 AS SELECT * FROM DBA_OBJECTS; 
  3. CREATE INDEX IDX_T1_ID ON T1(OBJECT_ID); 
  4. EXEC dbms_stats.gather_table_stats(ownname=>'SZT',tabname =>'T1'); 

第二章 实验环境测试

实验脚本如下:

  1. select * from ( 
  2. select * from t1  WHERE object_id>88500 or object_id in (1,2,3,4,5,6,7) 
  3. order by object_id) 
  4. where rownum<100; 

目的是通过单个索引,将优化器走索引位图转换与否的执行效率比较。

2.1 比较执行效率

首先测试默认情况下的执行计划:

  1. select * from ( 
  2. select * from t1  WHERE object_id>88500 or object_id in (1,2,3,4,5,6,7) 
  3. order by object_id) 
  4. where rownum<100; 

Oracle中索引位图转换的优势

可以看到,优化器没有对索引做位图转换,而是使用了OR扩展的方式。分别访问两部分的查询条件,并对其中的IN条件使用IN-LIST迭代的方式获取数据。

分析这样的优势:

IN条件中多个值会分别被访问并与索引中的数据作比较,条件中的多个值也不会访问索引多次,执行效率较高。通过逻辑读部分也能确定。

通过HINT,尝试让优化器走出索引位图转换方式:

  1. select /*+   OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1"
  2. BITMAP_TREE(@"SEL$2" "T1"@"SEL$2" OR(1 1 ("T1"."OBJECT_ID") 2 ("T1"."OBJECT_ID") 3 ("T1"."OBJECT_ID") 4 
  3. ("T1"."OBJECT_ID") 5 ("T1"."OBJECT_ID") 6 ("T1"."OBJECT_ID") 7 ("T1"."OBJECT_ID") 8 ("T1"."OBJECT_ID"))) */* from ( 
  4. select * from t1  WHERE object_id>88500 or object_id in (1,2,3,4,5,6,7) 
  5. order by object_id) 
  6. where rownum<100; 

Oracle中索引位图转换的优势

可以看到,每一次位图访问都只得到一个对应的IN条件值,且相同索引访问多次每次都消耗固定的逻辑读,据此分析当前场景下位图索引转换执行效率不佳。原因来自于索引的多次访问。

我们查看相应表上的索引信息:

Oracle中索引位图转换的优势

可以看到索引建立的原则就是唯一值与表数据1:1的情况。同时,由于采用了OBJECT_ID,其自增长特性,索引的聚簇因子比较小,属于相对高效的索引。

得出结论:在聚簇因子较小时,通过OR扩展、IN-LIST迭代的方式其执行效率高于索引位图转换。且优化器也能准确评估COST成本。

但实际生产环境中,大部分索引的聚簇因子没有这么高效。下面我们降低聚簇因子值及进行测试。

2.2 降低索引的聚簇因子:

让我们重新创建新表。实验脚本如下:

  1. CREATE TABLE T2 AS SELECT * FROM DBA_OBJECTS WHERE 1=2; 
  2. insert into t2 select * from dba_objects order by dbms_random.value; --随机插入 
  3. CREATE INDEX IDX_T2_ID ON T1(OBJECT_ID); 
  4. EXEC dbms_stats.gather_table_stats(ownname=>'SZT',tabname =>'T2'); 

通过打乱表数据的顺序,降低聚簇因子值。

Oracle中索引位图转换的优势

可以看到聚簇因子几乎接近于表中数据行数,且索引叶子块也有所增加。

2.2.1 比较执行效率

  1. select * from ( 
  2. select * from t2  WHERE object_id>88500 or object_id in (1,2,3,4,5,6,7) 
  3. order by object_id) 
  4. where rownum<100; 

Oracle中索引位图转换的优势

可以看到,默认情况下执行计划变为了索引位图转换的形式。

分析其优势:只进行了一次回表。

通过HINT让优化器走回原有执行计划:

  1. select * from ( 
  2. select /*+ USE_CONCAT(@"SEL$2" 8 OR_PREDICATES(1))  */ * from t2  WHERE object_id>88500 or object_id in (1,2,3,4,5,6,7) 
  3. order by object_id) 
  4. where rownum<100; 

Oracle中索引位图转换的优势

可以看到,由于回表了两次,且聚簇因子较大,其消耗的逻辑读已经逐渐接近于索引位图转换的方式了。

且分析其回表逻辑读:

  • 位图形式:134行回表,消耗147-16=131。
  • OR扩展:128行回表,消耗130-2=128。

回表的逻辑读十分接近。

总结:

索引位图转换的优势是减少回表次数。

OR扩展的优势是其IN-LIST迭代部分消耗逻辑读较低。

分析到此,我们已经基本明确不同方式的优劣了,但对实际的逻辑读消耗对比还不够确定。

下面让我们增大查询的条件范围。

2.2.2 增大查询条件范围

  1. select * from ( 
  2. select * from t2  WHERE object_id>88450 or object_id in (1,2,3,4,5,6,7) 
  3. order by object_id) 
  4. where rownum<100; 

Oracle中索引位图转换的优势

测试OR扩展:

  1. select /*+        USE_CONCAT(@"SEL$2" 8 OR_PREDICATES(1))      */* from ( 
  2. select * from t2  WHERE object_id>88450 or object_id in (1,2,3,4,5,6,7) 
  3. order by object_id) 
  4. where rownum<100; 

Oracle中索引位图转换的优势

可以看到,当增大查询范围值后,两种不同执行计划其实际的消耗越来越接近了,最后通过索引位图转换的方式其执行效率甚至高于原有的OR扩展的形式。因此我们在判断执行效率时,还是要具体情况具体分析。

分析回表的逻辑读开销:

  • 位图形式:172行回表,消耗180-16=164
  • OR扩展:166行回表,消耗168-2=166

据此我们又可以确定,传统的回表方式其实际的资源开销高于索引位图转换后的回表方式。这又是索引位图转换的一大好处。

得出结论:

聚簇因子越大的索引,其越能在索引位图转换的方式中受益。因为其只需要回表一次。

索引位图转换后的回表,其消耗的资源开销会低于传统的回表方式。这也是索引位图转换的优势之一。

第三章 总结

以上,我们通过3个测试例子,验证的不同场景下的执行计划表现。

关于开头部分我们的疑问,可以很明确做答了。

1.索引位图转换和传统的OR扩展、IN-LIST迭代等形式、其执行效率要具体情况具体分析。主要受影响于相关索引上的聚簇因子值。

2.索引位图转换的优势是一次性统一回表,ROWID回表的开销也会略低于传统的形式。

3. IN-LIST迭代的优势是对于IN后面条件多个值的访问,其实际资源开销较低。

墨天轮原文链接:https://www.modb.pro/db/25952(复制链接至浏览器或点击文末阅读原文查看)

关于作者

张程,云和恩墨SQL优化工程师,长期服务于金融、保险行业。现负责:公司Oracle、SQLServer、MySQL数据库优化方面的技术工作;公司SQL审核软件SQM的审核相关工作。热衷于性能优化的学习与分享。

原文链接:https://mp.weixin.qq.com/s/zSvvenfX1g6A850OiQ2lcQ

延伸 · 阅读

精彩推荐
  • OracleExcel导入oracle的几种方法

    Excel导入oracle的几种方法

    本篇文章是对Excel导入oracle的几种方法进行了详细的分析介绍,需要的朋友参考下 ...

    oracle教程网4722019-11-26
  • OracleOracle数据库的字段约束创建和维护示例

    Oracle数据库的字段约束创建和维护示例

    本篇文章主要介绍了Oracle数据库的字段约束创建和维护示例,可以创建,添加,删除等约束,感兴趣的小伙伴们可以参考一下。 ...

    在路上的JavaCoder-尹骑3742020-01-10
  • OracleOracle中使用Rownum分页详细例子

    Oracle中使用Rownum分页详细例子

    这篇文章主要介绍了Oracle中使用Rownum分页详细例子,本文将分别展示使用rownum伪列和row_number()分析函数来完成Oracle数据分页操作的具体使用方法,需要的朋友...

    Beanbee4312019-12-11
  • OracleOracle数据库的备份及恢复策略研究

    Oracle数据库的备份及恢复策略研究

    恢复丢失的数据库文件在很大程度上取决于所采用的备份策略。本文从恢复的灵活性出发,对Oracle8数据库的备份及恢复策略进行了探讨,并给出了Windows2...

    Oracle教程网4172019-10-25
  • OracleORACLE实现自定义序列号生成的方法

    ORACLE实现自定义序列号生成的方法

    这篇文章主要为大家详细介绍了ORACLE实现自定义序列号生成的方法,具有一定的参考价值,感兴趣的小伙伴们可以参考一下 ...

    靓仔小伙计7512019-12-31
  • Oracle简析Oracle数据库常见问题及解决方案

    简析Oracle数据库常见问题及解决方案

    这篇文章主要介绍了Oracle数据库常见问题及解决方案,总结了一些在Oracle数据库使用过程中的常见问题,并对其进行了分析,给出了解决方案,需要的朋友...

    lijiao3152019-12-20
  • OracleOracle merge合并更新函数实例详解

    Oracle merge合并更新函数实例详解

    这篇文章主要给大家介绍了关于Oracle merge合并更新函数的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,...

    smileNicky2262020-04-11
  • OracleOracle索引质量介绍和分析脚本分享

    Oracle索引质量介绍和分析脚本分享

    这篇文章主要介绍了Oracle索引质量介绍和分析脚本分享,索引质量的高低对数据库整体性能有着直接的影响,本文给出了演示以及索引创建的基本指导原则,最...

    JUNJIE2642019-12-16