首页 / 更多教程 / 巧用函数索引性能调优

巧用函数索引性能调优

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了巧用函数索引性能调优，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10488字，纯文字阅读大概需要15分钟。

内容图文

   今天调了一条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 = ‘7002321‘
 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                            ‘MASK=ANYINTERACT‘) = ‘TRUE‘)
 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"=‘7002321‘)
  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)),‘MASK=ANYINTERACT‘)=‘TRUE‘)
  14 - access("A"."CLASSIFY_ID"=‘7002321‘)
统计信息
----------------------------------------------------------
      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 = ‘7002321‘
 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                            ‘MASK=ANYINTERACT‘) = ‘TRUE‘)
 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"=‘7002321‘)
  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)),‘MASK=ANYINTERACT‘)=‘TRUE‘)
  14 - access("A"."CLASSIFY_ID"=‘7002321‘)
统计信息
----------------------------------------------------------
      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;
70023861
70023693
700238113
700251316
700234918
700233336
7002474166
7002502276
70023451131
70023441423
70023821709
70023591791
70025106340
70023746684
70023587286
70023618750
700237911189
700234012622
700247312717
700234814901
700236015722
700233517939
700233621492
700250028304
700236342883
700234349865
700247251327
700232176098
700237389485
7002515110765
7002378128512
7002380147776
7002499235166
7002370271190
7002501370439
7002398456259
7002496830986
70024014361079

尝试用第二种方法：删除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 = ‘7002321‘
 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                            ‘MASK=ANYINTERACT‘) = ‘TRUE‘)
 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"=‘7002321‘)
   7 - filter("A"."CLASSIFY_ID"=‘7002321‘)
   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)),‘MASK=ANYINTERACT‘)=‘TRUE‘)
统计信息
----------------------------------------------------------
       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 !=‘7002401‘ THEN CLASSIFY_ID ELSE NULL END) nologging;
1. 上面的SQL执行也是非常快。
2. 根据CLASSIFY_ID的代码需要改一下
if(CLASSIFY_ID = ‘7002401‘) {--如果是这个值，则走全部扫描
   select *  from DM_ASSET where CLASSIFY_ID = ‘7002321‘;
} else {--如果是这个值，则走索引
  select * from  DM_ASSET where (CASE WHEN CLASSIFY_ID !=‘7002401‘ 
THEN CLASSIFY_ID ELSE NULL END)=‘7002401‘
}

原文：http://blog.csdn.net/stevendbaguo/article/details/21029787

内容总结

以上是互联网集市为您收集整理的巧用函数索引性能调优全部内容，希望文章能够帮你解决巧用函数索引性能调优所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1252729.html

来源：【匿名】