TravelskyDBA

近期听到一位Oracle业界前辈，说道:”SQL优化，关键在写法”。我在生产过程中碰到的SQL问题，因为写法有误造成SQL产生严重的性能问题非常多，开发人员可能更关心我的代码功能是否实现，SQL上线前因测试系统环境、数据量与生产存在差异，导致SQL在生产上实际运行时效率远远小于预期，前几天，碰到如下这个SQL案例，个人感觉非常典型，SQL写法很关键！

SELECT count(*)
FROM pnrresultdata
WHERE TO_NUMBER(SYSDATE - CREATEtime)* 86400 > 5*60
  AND TO_NUMBER(SYSDATE - CREATEtime)* 86400 < 4*60*60
  AND username ='baosheng_dps'
  AND (getTime >0)
  AND postType='R'
  AND isSuccess=0

SQL逻辑很简单，只是单纯输出一下符合条件的count(*)，资源消耗如下图所示

然而，表中CREATETIME字段选择性很好，NUM_ROWS:64186660 NDV：2497536，此字段上没有索引，但是因为写法问题，常规组合索引中包含此字段也没有办法利用上索引，非常常见的原因：字段上参与了运算。

将SQL等价改写为：

SELECT count(*)
FROM pnrresultdata
WHERE CREATETIME < SYSDATE - interval '5' MINUTE
  AND CREATETIME > SYSDATE - interval '4' hour
  AND username ='baosheng_dps'
  AND (getTime >0)
  AND postType='R'
  AND isSuccess=0

原SQL执行计划，采用了ISSUCCESS列上的低效索引：

-------------------------------------
SQL_ID  76d58v13x8z90, child number 0
-------------------------------------
SELECT count(*)
FROM pnrresultdata
WHERE TO_NUMBER(SYSDATE - CREATEtime)* 86400 > 5*60
  AND TO_NUMBER(SYSDATE - CREATEtime)* 86400 < 4*60*60
  AND username ='baosheng_dps'
  AND (getTime >0)
  AND postType='R'
  AND isSuccess=0

Plan hash value: 3822156680

-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |      1 |        |       | 14814 (100)|          |      1 |00:00:01.03 |     296K|   4761 |
|   1 |  SORT AGGREGATE              |                         |      1 |      1 |    26 |            |          |      1 |00:00:01.03 |     296K|   4761 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| PNRRESULTDATA           |      1 |      1 |    26 | 14814   (1)| 00:02:58 |    429 |00:00:01.03 |     296K|   4761 |
|*  3 |    INDEX RANGE SCAN          | PNRRESULTDATA_ISSUCCESS |      1 |    452K|       |  2765   (1)| 00:00:34 |    413K|00:00:00.06 |    2949 |     34 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------

2 - filter(("USERNAME"='baosheng_dps' AND "POSTTYPE"='R' AND TO_NUMBER(SYSDATE@!-"CREATETIME")*86400>300 AND
              TO_NUMBER(SYSDATE@!-"CREATETIME")*86400<14400 AND "GETTIME">0))
   3 - access("ISSUCCESS"=0)

改写后，因其他字段缺失索引，目前状态仍不为最佳，同时出现了BITMAP CONVERSION

-------------------------------------
SQL_ID  00xz7m7u33wgg, child number 1
-------------------------------------
SELECT count(*)
FROM pnrresultdata
WHERE CREATETIME < SYSDATE - interval '5' MINUTE
  AND CREATETIME > SYSDATE - interval '4' hour
  AND username ='baosheng_dps'
  AND (getTime >0)
  AND postType='R'
  AND isSuccess=0

Plan hash value: 2537524006

------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                          |      1 |        |       |  9313 (100)|          |      1 |00:00:00.23 |    4339 |   1531 |
|   1 |  SORT AGGREGATE                    |                          |      1 |      1 |    26 |            |          |      1 |00:00:00.23 |    4339 |   1531 |
|*  2 |   FILTER                           |                          |      1 |        |       |            |          |    429 |00:00:00.23 |    4339 |   1531 |
|*  3 |    TABLE ACCESS BY INDEX ROWID     | PNRRESULTDATA            |      1 |    429 | 11154 |  9313   (1)| 00:01:52 |    429 |00:00:00.23 |    4339 |   1531 |
|   4 |     BITMAP CONVERSION TO ROWIDS    |                          |      1 |        |       |            |          |   1164 |00:00:00.23 |    3477 |   1531 |
|   5 |      BITMAP AND                    |                          |      1 |        |       |            |          |      1 |00:00:00.23 |    3477 |   1531 |
|   6 |       BITMAP CONVERSION FROM ROWIDS|                          |      1 |        |       |            |          |      6 |00:00:00.06 |    1937 |      0 |
|*  7 |        INDEX RANGE SCAN            | PNRRESULTDATA_ISSUCCESS  |      1 |    452K|       |  2765   (1)| 00:00:34 |    288K|00:00:00.03 |    1937 |      0 |
|   8 |       BITMAP CONVERSION FROM ROWIDS|                          |      1 |        |       |            |          |      2 |00:00:00.17 |    1540 |   1531 |
|   9 |        SORT ORDER BY               |                          |      1 |        |       |            |          |    177K|00:00:00.16 |    1540 |   1531 |
|* 10 |         INDEX RANGE SCAN           | PNRRESULTDATA_CREATETIME |      1 |    452K|       |  1593   (1)| 00:00:20 |    177K|00:00:00.10 |    1540 |   1531 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------

结合字段的选择性与在索引中的过滤必要，需要创建:

create index t1_idx01 on t1(username,postType,isSuccess,CREATEtime) &tablespace parallel ∥