首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 数据库 > 正文

巧用函数索引性能调优

作者：2yuheng | 来源：互联网 | 2018-06-02 01:10
今天调了一条SQL，执行计划中出现BITMAPCONVERSION，检查了下索引，没有位图索引

今天调了一条SQL，执行计划中出现BITMAP CONVERSION，检查了下索引，没有位图索引

今天调了一条SQL，执行计划中出现BITMAP CONVERSION，检查了下索引，没有位图索引啊。原来是Oracle有时候会选择将B-Tree索引进行BITMAP转换来进行SQL执行，从而导致极其恶劣的执行计划。在下列计划中BITMAP CONVERSION FROM / TO ROWIDS 就是进行了位图转换后的执行计划：

SQL> SELECT *

2 FROM (SELECT A.ASSET_ID,
3 C.CLASSIFY_CODE,
4 C.CLASSIFY_NAME,
5 V.NOMINAL_VOLTAGE BASE_NOMINAL_VOLTAGE,
6 TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(A.G3E_GEOMETRY)) AS GML
7 FROM DM_ASSET A, DM_CLASSIFY C, DM_BASE_VOLTAGE V
8 WHERE A.CLASSIFY_ID = C.CLASSIFY_ID
9 AND A.BASE_VOLTAGE_ID = V.BASE_VOLTAGE_ID(+)
10 AND C.CLASSIFY_ID = &＃39;7002321&＃39;
11 AND SDO_RELATE(A.G3E_GEOMETRY,
12 SDO_GEOMETRY(3003,
13 4326,
14 NULL,
15 SDO_ELEM_INFO_ARRAY(1, 1003, 1),
16 SDO_ORDINATE_ARRAY(113.93897922622,
17 22.806658666304,
18 0,
19 114.38475977774,
20 22.806658666304,
21 0,
22 114.38475977774,
23 22.42623522295,
24 0,
25 113.93897922622,
26 22.42623522295,
27 0,
28 113.93897922622,
29 22.806658666304,
30 0)),
31 &＃39;MASK=ANYINTERACT&＃39;) = &＃39;TRUE&＃39;)
32 WHERE ROWNUM <= 500;
已选择500行。
已用时间: 00: 00: 06.71
执行计划
----------------------------------------------------------
Plan hash value: 1161815771
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 130K| 263 (1)| 00:00:04 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 501 | 130K| 263 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL | DM_BASE_VOLTAGE | 81 | 729 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 501 | 64629 | 259 (1)| 00:00:04 |
| 5 | TABLE ACCESS BY INDEX ROWID | DM_CLASSIFY | 1 | 23 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_DM_CLASSIFY_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | DM_ASSET | 501 | 53106 | 259 (1)| 00:00:04 |
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 9 | BITMAP AND | | | | | |
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 11 | SORT ORDER BY | | | | | |
|* 12 | DOMAIN INDEX | IDX_DM_ASSET_SPL | 50100 | | 0 (0)| 00:00:01 |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 14 | INDEX RANGE SCAN | IDX_ASSET_CLASSIFY_ID | 50100 | | 135 (1)| 00:00:02 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=500)
2 - access("A"."BASE_VOLTAGE_ID"="V"."BASE_VOLTAGE_ID"(+))
6 - access("C"."CLASSIFY_ID"=&＃39;7002321&＃39;)
12 - access("MDSYS"."SDO_RELATE"("A"."G3E_GEOMETRY","MDSYS"."SDO_GEOMETRY"(3003,4326,NULL,"SDO_ELEM
_INFO_ARRAY"(1,1003,1),"SDO_ORDINATE_ARRAY"(113.93897922622,22.806658666304,0,114.38475977774,22.8066
58666304,0,114.38475977774,22.42623522295,0,113.93897922622,22.42623522295,0,113.93897922622,22.80665
8666304,0)),&＃39;MASK=ANYINTERACT&＃39;)=&＃39;TRUE&＃39;)
14 - access("A"."CLASSIFY_ID"=&＃39;7002321&＃39;)
统计信息
----------------------------------------------------------
76096 recursive calls
6004 db block gets
78944 consistent gets
0 physical reads
0 redo size
100625 bytes sent via SQL*Net to client
748 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
500 rows processed

到网上找了下，有两种解决方法，一种是将_b_tree_bitmap_plans设置为false，另一种是将选择性差的索引去掉。

尝试用第一种方法：

SQL> alter session set "_b_tree_bitmap_plans"=false;
SQL> SELECT *
2 FROM (SELECT A.ASSET_ID,
3 C.CLASSIFY_CODE,
4 C.CLASSIFY_NAME,
5 V.NOMINAL_VOLTAGE BASE_NOMINAL_VOLTAGE,
6 TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(A.G3E_GEOMETRY)) AS GML
7 FROM DM_ASSET A, DM_CLASSIFY C, DM_BASE_VOLTAGE V
8 WHERE A.CLASSIFY_ID = C.CLASSIFY_ID
9 AND A.BASE_VOLTAGE_ID = V.BASE_VOLTAGE_ID(+)
10 AND C.CLASSIFY_ID = &＃39;7002321&＃39;
11 AND SDO_RELATE(A.G3E_GEOMETRY,
12 SDO_GEOMETRY(3003,
13 4326,
14 NULL,
15 SDO_ELEM_INFO_ARRAY(1, 1003, 1),
16 SDO_ORDINATE_ARRAY(113.93897922622,
17 22.806658666304,
18 0,
19 114.38475977774,
20 22.806658666304,
21 0,
22 114.38475977774,
23 22.42623522295,
24 0,
25 113.93897922622,
26 22.42623522295,
27 0,
28 113.93897922622,
29 22.806658666304,
30 0)),
31 &＃39;MASK=ANYINTERACT&＃39;) = &＃39;TRUE&＃39;)
32 WHERE ROWNUM <= 500;
已选择500行。
已用时间: 00: 00: 06.51
执行计划
----------------------------------------------------------
Plan hash value: 1161815771
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 130K| 263 (1)| 00:00:04 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 501 | 130K| 263 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL | DM_BASE_VOLTAGE | 81 | 729 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 501 | 64629 | 259 (1)| 00:00:04 |
| 5 | TABLE ACCESS BY INDEX ROWID | DM_CLASSIFY | 1 | 23 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_DM_CLASSIFY_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | DM_ASSET | 501 | 53106 | 259 (1)| 00:00:04 |
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 9 | BITMAP AND | | | | | |
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 11 | SORT ORDER BY | | | | | |
|* 12 | DOMAIN INDEX | IDX_DM_ASSET_SPL | 50100 | | 0 (0)| 00:00:01 |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 14 | INDEX RANGE SCAN | IDX_ASSET_CLASSIFY_ID | 50100 | | 135 (1)| 00:00:02 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=500)
2 - access("A"."BASE_VOLTAGE_ID"="V"."BASE_VOLTAGE_ID"(+))
6 - access("C"."CLASSIFY_ID"=&＃39;7002321&＃39;)
12 - access("MDSYS"."SDO_RELATE"("A"."G3E_GEOMETRY","MDSYS"."SDO_GEOMETRY"(3003,4326,NULL,"SDO_ELEM
_INFO_ARRAY"(1,1003,1),"SDO_ORDINATE_ARRAY"(113.93897922622,22.806658666304,0,114.38475977774,22.8066
58666304,0,114.38475977774,22.42623522295,0,113.93897922622,22.42623522295,0,113.93897922622,22.80665
8666304,0)),&＃39;MASK=ANYINTERACT&＃39;)=&＃39;TRUE&＃39;)
14 - access("A"."CLASSIFY_ID"=&＃39;7002321&＃39;)
统计信息
----------------------------------------------------------
76096 recursive calls
6002 db block gets
78944 consistent gets
0 physical reads
0 redo size
100625 bytes sent via SQL*Net to client
748 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
500 rows processed

DM_ASSET有7百多万的数据，而CLASSIFY_ID的值只有39个，其中38个值可以走索引，一个值的数量非常多，典型的数据不均匀。
select CLASSIFY_ID,count(1) from DM_ASSET group by CLASSIFY_ID order by 2;
7002386 1
7002369 3
7002381 13
7002513 16
7002349 18
7002333 36
7002474 166
7002502 276
7002345 1131
7002344 1423
7002382 1709
7002359 1791
7002510 6340
7002374 6684
7002358 7286
7002361 8750
7002379 11189
7002340 12622
7002473 12717
7002348 14901
7002360 15722
7002335 17939
7002336 21492
7002500 28304
7002363 42883
7002343 49865
7002472 51327
7002321 76098
7002373 89485
7002515 110765
7002378 128512
7002380 147776
7002499 235166
7002370 271190
7002501 370439
7002398 456259
7002496 830986
7002401 4361079

尝试用第二种方法：删除CLASSIFY_ID上的索引。
SQL> SELECT *
2 FROM (SELECT A.ASSET_ID,
3 C.CLASSIFY_CODE,
4 C.CLASSIFY_NAME,
5 V.NOMINAL_VOLTAGE BASE_NOMINAL_VOLTAGE,
6 TO_CHAR(SDO_UTIL.TO_GMLGEOMETRY(A.G3E_GEOMETRY)) AS GML
7 FROM DM_ASSET A, DM_CLASSIFY C, DM_BASE_VOLTAGE V
8 WHERE A.CLASSIFY_ID = C.CLASSIFY_ID
9 AND A.BASE_VOLTAGE_ID = V.BASE_VOLTAGE_ID(+)
10 AND C.CLASSIFY_ID = &＃39;7002321&＃39;
11 AND SDO_RELATE(A.G3E_GEOMETRY,
12 SDO_GEOMETRY(3003,
13 4326,
14 NULL,
15 SDO_ELEM_INFO_ARRAY(1, 1003, 1),
16 SDO_ORDINATE_ARRAY(113.93897922622,
17 22.806658666304,
18 0,
19 114.38475977774,
20 22.806658666304,
21 0,
22 114.38475977774,
23 22.42623522295,
24 0,
25 113.93897922622,
26 22.42623522295,
27 0,
28 113.93897922622,
29 22.806658666304,
30 0)),
31 &＃39;MASK=ANYINTERACT&＃39;) = &＃39;TRUE&＃39;)
32 WHERE ROWNUM <= 500;
已选择500行。
已用时间: 00: 00: 00.50
执行计划
----------------------------------------------------------
Plan hash value: 4025821404
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 130K| 10563 (1)| 00:02:07 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 501 | 130K| 10563 (1)| 00:02:07 |
| 3 | TABLE ACCESS FULL | DM_BASE_VOLTAGE | 81 | 729 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 501 | 64629 | 10560 (1)| 00:02:07 |
| 5 | TABLE ACCESS BY INDEX ROWID| DM_CLASSIFY | 1 | 23 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_DM_CLASSIFY_ID | 1 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| DM_ASSET | 501 | 53106 | 10560 (1)| 00:02:07 |
|* 8 | DOMAIN INDEX | IDX_DM_ASSET_SPL | | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=500)
2 - access("A"."BASE_VOLTAGE_ID"="V"."BASE_VOLTAGE_ID"(+))
6 - access("C"."CLASSIFY_ID"=&＃39;7002321&＃39;)
7 - filter("A"."CLASSIFY_ID"=&＃39;7002321&＃39;)
8 - access("MDSYS"."SDO_RELATE"("A"."G3E_GEOMETRY","MDSYS"."SDO_GEOMETRY"(3003,4326,NULL,"
SDO_ELEM_INFO_ARRAY"(1,1003,1),"SDO_ORDINATE_ARRAY"(113.93897922622,22.806658666304,0,114.384
75977774,22.806658666304,0,114.38475977774,22.42623522295,0,113.93897922622,22.42623522295,0,
113.93897922622,22.806658666304,0)),&＃39;MASK=ANYINTERACT&＃39;)=&＃39;TRUE&＃39;)
统计信息
----------------------------------------------------------
3965 recursive calls
6006 db block gets
30388 consistent gets
3 physical reads
340 redo size
94355 bytes sent via SQL*Net to client
748 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
500 rows processed


问题又来了，删除索引后，这个SQL是快了，但原本根据CLASSIFY_ID走索引的语句性能出了问题。曾经在itpub上看到newid这么用，建一个函数索引：
CREATE INDEX IDX_ASSET_CLASSIFY_ID ON DM_ASSET(CASE WHEN CLASSIFY_ID !=&＃39;7002401&＃39; THEN CLASSIFY_ID ELSE NULL END) nologging;
1. 上面的SQL执行也是非常快。
2. 根据CLASSIFY_ID的代码需要改一下
if(CLASSIFY_ID = &＃39;7002401&＃39;) {--如果是这个值，则走全部扫描
select * from DM_ASSET where CLASSIFY_ID = &＃39;7002321&＃39;;
} else {--如果是这个值，则走索引
select * from DM_ASSET where (CASE WHEN CLASSIFY_ID !=&＃39;7002401&＃39;
THEN CLASSIFY_ID ELSE NULL END)=&＃39;7002401&＃39;
}
推荐阅读
  • sql

    使用Power Design和SQL Server进行数据库反向工程的方法详解

    本文介绍了如何使用Power Design（PD）和SQL Server进行数据库反向工程的方法。通过创建数据源、选择要反向工程的数据表，PD可以生成物理模型，进而生成所需的概念模型。该方法适用于SQL Server数据库，对于其他数据库是否适用尚不确定。详细步骤和操作说明可参考本文内容。 ... [详细]
  • mysql

    mysql分组排序_在MySQL中实现分组排序功能

    在数据分析工作中，我们通常会遇到这样的问题，一个业务部门由若干业务组构成，需要筛选出每个业务组里业绩前N名的业务员。这其实是一个分组排序的 ... [详细]
  • mysql

    PHP中的MySQL函数库及其常用函数介绍

    本文由编程笔记小编整理，介绍了PHP中的MySQL函数库及其常用函数，包括mysql_connect、mysql_error、mysql_select_db、mysql_query、mysql_affected_row、mysql_close等。希望对读者有一定的参考价值。 ... [详细]
  • sql

    Oracle中tnsnames.ora的作用和配置方法

    本文介绍了Oracle数据库中tnsnames.ora文件的作用和配置方法。tnsnames.ora文件在数据库启动过程中会被读取，用于解析LOCAL_LISTENER，并且与侦听无关。文章还提供了配置LOCAL_LISTENER和1522端口的示例，并展示了listener.ora文件的内容。 ... [详细]
  • mysql

    Spring特性实现接口多类的动态调用详解

    Spring特性实现接口多类的动态调用详解
    本文详细介绍了如何使用Spring特性实现接口多类的动态调用。通过对Spring IoC容器的基础类BeanFactory和ApplicationContext的介绍，以及getBeansOfType方法的应用，解决了在实际工作中遇到的接口及多个实现类的问题。同时，文章还提到了SPI使用的不便之处，并介绍了借助ApplicationContext实现需求的方法。阅读本文，你将了解到Spring特性的实现原理和实际应用方式。 ... [详细]
  • sql

    Java String与StringBuffer的区别及其应用场景

    Java String与StringBuffer的区别及其应用场景
    本文主要介绍了Java中String和StringBuffer的区别，String是不可变的，而StringBuffer是可变的。StringBuffer在进行字符串处理时不生成新的对象，内存使用上要优于String类。因此，在需要频繁对字符串进行修改的情况下，使用StringBuffer更加适合。同时，文章还介绍了String和StringBuffer的应用场景。 ... [详细]
  • sql

    Oracle分析函数first_value()和last_value()的用法及原理

    Oracle分析函数first_value()和last_value()的用法及原理
    本文介绍了Oracle分析函数first_value()和last_value()的用法和原理，以及在查询销售记录日期和部门中的应用。通过示例和解释，详细说明了first_value()和last_value()的功能和不同之处。同时，对于last_value()的结果出现不一样的情况进行了解释，并提供了理解last_value()默认统计范围的方法。该文对于使用Oracle分析函数的开发人员和数据库管理员具有参考价值。 ... [详细]
  • sql

    MyBatis错题分析解析及注意事项

    MyBatis错题分析解析及注意事项
    本文对MyBatis的错题进行了分析和解析，同时介绍了使用MyBatis时需要注意的一些事项，如resultMap的使用、SqlSession和SqlSessionFactory的获取方式、动态SQL中的else元素和when元素的使用、resource属性和url属性的配置方式、typeAliases的使用方法等。同时还指出了在属性名与查询字段名不一致时需要使用resultMap进行结果映射，而不能使用resultType。 ... [详细]
  • sql

    ASP.NET Tips: 获取插入记录的ID的方法详解

    本文详细介绍了在ASP.NET中获取插入记录的ID的几种方法，包括使用SCOPE_IDENTITY()和IDENT_CURRENT()函数，以及通过ExecuteReader方法执行SQL语句获取ID的步骤。同时，还提供了使用这些方法的示例代码和注意事项。对于需要获取表中最后一个插入操作所产生的ID或马上使用刚插入的新记录ID的开发者来说，本文提供了一些有用的技巧和建议。 ... [详细]
  • sql

    VB.NET在线急等问题解决方法，如何统计数据库字段下的数据并显示在文本框里？

    本文介绍了一个在线急等问题解决方法，即如何统计数据库中某个字段下的所有数据，并将结果显示在文本框里。作者提到了自己是一个菜鸟，希望能够得到帮助。作者使用的是ACCESS数据库，并且给出了一个例子，希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句，得到的结果是650，但不知道如何得到560。希望能够得到解决方案。 ... [详细]
  • sql

    SpringJdbcTemplate的使用详解

    本文详细介绍了Spring的JdbcTemplate的使用方法，包括执行存储过程、存储函数的call()方法，执行任何SQL语句的execute()方法，单个更新和批量更新的update()和batchUpdate()方法，以及单查和列表查询的query()和queryForXXX()方法。提供了经过测试的API供使用。 ... [详细]
  • sql

    高质量SQL书写的30条建议

    高质量SQL书写的30条建议
    本文提供了30条关于优化SQL的建议，包括避免使用select *，使用具体字段，以及使用limit 1等。这些建议是基于实际开发经验总结出来的，旨在帮助读者优化SQL查询。 ... [详细]
  • mysql

    如何查看mysql的安装路径

    本文介绍了通过mysql命令查看mysql的安装路径的方法，提供了相应的sql语句，并希望对读者有参考价值。 ... [详细]
  • sql

    问题2：重新命名或者移动数据文件、日志文件到新的位置

    本文讨论了在数据库打开和关闭状态下，重新命名或移动数据文件和日志文件的情况。针对性能和维护原因，需要将数据库文件移动到不同的磁盘上或重新分配到新的磁盘上的情况，以及在操作系统级别移动或重命名数据文件但未在数据库层进行重命名导致报错的情况。通过三个方面进行讨论。 ... [详细]
  • sql

    用SQL语句怎么把已存在的列加上IDENTITY(1,1)属性

    ALTERTABLE通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。语法ALTERTABLEtable{[ALTERCOLUMNcolu ... [详细]
author-avatar
2yuheng
这个家伙很懒，什么也没留下！
Tags | 热门标签
RankList | 热门文章
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有