ORACLE:由位图索引引发的sql问题

太过爱你忘了你带给我的痛 2022-09-22 04:56 151阅读 0赞

原来系统中运行2、3分钟的sql，突然2天跑不完

sql如下：

SELECT info.\*, keyinfo.KEYSORT AS keysort  
  FROM (SELECT rep.SrhTime AS rq,  
               bus.providerid AS cq,  
               bus.srhpc AS pc,  
               cor.srhzs AS zs,  
               act.actname AS th,  
               cor.county\_seat AS ct,  
               cor.cyd\_type AS ib,  
               cor.ten\_medal AS me,  
               cor.srhas AS sa,  
               bus.srhbz AS bz,  
               cor.srhjy AS jy,  
               cor.srhve AS ve,  
               rep.enddate AS et,  
               bus.go\_type AS hd,  
               bus.go\_name AS bq,  
               bus.name\_318 AS bd,  
               rep.srhcc AS cc,  
               bus.srhcm AS cm,  
               bus.srhcy AS cy,  
               cor.srhcz AS cz,  
               bus.srhcd AS cd,  
               cor.deposit\_state AS gd,  
               weisite AS ss,  
               cor.srhveunit AS vu,  
               cor.longitude AS lo,  
               cor.latitude AS lt,  
               bus.advance\_payment AS ors,  
               '' AS pr,  
               bus.sales\_amount AS ta,  
               bus.sales\_volume AS tc,  
               cor.qq AS qq,  
               '' AS tf,  
               '' AS mt,  
               '' AS kn,  
               '' AS pn,  
               cor.telephone AS hb,  
               cor.fax AS he,  
               cor.SALES\_VOLUME AS tv,  
               bus.sales\_count AS tu,  
               '' AS ca,  
               cor.ishighquality AS fs,  
               bus.supply\_count AS gy,  
               cor.userid AS ud,  
               usrcount.states AS yp,  
               bus.enquiry\_status AS xp,  
               cor.mobile\_telephone AS ml,  
               TRUNC (  
                    (    TO\_NUMBER (  
                              cor.authendate  
                            - TO\_DATE ('1970-01-01 00:00:00',  
                                       'yyyy-mm-dd hh24:mi:ss'))  
                       \* 86400  
                     - 8 \* 3600)  
                  / 30)  
                  AS tb,  
               bus.pubtype AS pt,  
               cor.srhbu AS bu,  
               bus.srhtp AS tp,  
               bus.srhco AS co,  
               bus.srhcb AS cb,  
               bus.srhla AS la,  
               cor.groupid AS gn,  
               cor.shopname AS sn,  
               bus.is\_support\_trade AS zf,  
               bus.mm AS mm,  
               rep.srhmi AS mi,  
               REPLACE (bus.srhtitle, CHR (26), '') AS bt,  
               REPLACE (bus.srhcontent, CHR (26), '') AS jj,  
               REPLACE (cor.srhintro, CHR (26), '') AS intro,  
               bus.Yview360 || ',' || bus.srhim AS im,  
               'M' || rep.bc\_id AS id,  
               cor.srhtitle AS qy,  
               cor.username AS un,  
               cor.srhvn AS vn,  
               cor.srhtm AS tm,  
               bus.srhup AS UP,  
               bus.srhba AS ba,  
               bus.srhpm AS pm,  
               bus.srhbb || '|' || bus.actid AS bb,  
               bus.bc\_id AS infoid,  
               cor.mainpro AS zy,  
               '' AS pa,  
               bus.srhqu AS qu,  
               cor.srhsl AS sl,  
               '' AS ag,  
               '' AS ty,  
               bus.srhmo AS mo,  
               TRUNC (  
                    (    TO\_NUMBER (  
                              cor.srhsu  
                            - TO\_DATE ('1970-01-01 00:00:00',  
                                       'yyyy-mm-dd hh24:mi:ss'))  
                       \* 86400  
                     - 8 \* 3600)  
                  / 30)  
                  AS su,  
               cor.user\_level AS ul,  
               bus.evaluate\_count AS ec,  
               TRUNC (  
                    (    TO\_NUMBER (  
                              bus.searchdate  
                            - TO\_DATE ('1970-01-01 00:00:00',  
                                       'yyyy-mm-dd hh24:mi:ss'))  
                       \* 86400  
                     - 8 \* 3600)  
                  / 30)  
                  AS pq,  
               cor.contacter  
          FROM (SELECT /\*+ index(aa  IDX\_BUSIN\_HOT\_BITMAP\_SEARCHS IDX\_BUSIN\_HOT\_BITMAP\_OPERS) \*/ \*  
                  FROM busin\_hot aa  
                 WHERE     srhcc = '001'  
                       AND searchstate = '1'  
                       AND operstate = '1'  
                       AND loadstates = 'H'  
                       AND searchdate <=  
                              TO\_DATE ('20150901 23:59:59',  
                                       'yyyymmdd hh24:mi:ss')  
                       AND searchdate >=  
                              TO\_DATE ('20150725 00:00:00',  
                                       'yyyymmdd hh24:mi:ss')) rep  
               LEFT JOIN busin\_srh bus ON rep.bc\_id = bus.bc\_id  
               LEFT JOIN provider\_srh cor ON bus.providerid = cor.providerid  
               LEFT JOIN busin\_activity act ON bus.actid = act.actid  
               LEFT JOIN user\_averagequalitylog usrcount  
                  ON (cor.providerid = usrcount.providerid)) info  
       LEFT JOIN keyword\_info\_srh keyinfo  
          ON (info.infoid = keyinfo.infoid AND keyinfo.infotype = '0')

执行计划：

Plan hash value: 1031842883
    ----------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                               | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                        |                              |       |       | 65385 (100)|          |       |       |
    |   1 |  NESTED LOOPS OUTER                     |                              |    50 |   235K| 65385   (2)| 00:13:05 |       |       |
    |*  2 |   HASH JOIN RIGHT OUTER                 |                              |    50 |   232K| 65234   (2)| 00:13:03 |       |       |
    |   3 |    MAT_VIEW ACCESS FULL                 | BUSIN_ACTIVITY               |    24 |   336 |     3   (0)| 00:00:01 |       |       |
    |   4 |    NESTED LOOPS OUTER                   |                              |    10 | 23760 | 65231   (2)| 00:13:03 |       |       |
    |   5 |     NESTED LOOPS OUTER                  |                              |    10 | 23660 | 21806   (1)| 00:04:22 |       |       |
    |   6 |      NESTED LOOPS OUTER                 |                              |    10 | 21080 | 21786   (1)| 00:04:22 |       |       |
    |   7 |       PARTITION RANGE ITERATOR          |                              |    10 |   890 | 21746   (1)| 00:04:21 |   140 |   142 |
    |*  8 |        TABLE ACCESS BY LOCAL INDEX ROWID| BUSIN_HOT                    |    10 |   890 | 21746   (1)| 00:04:21 |   140 |   142 |
    |   9 |         BITMAP CONVERSION TO ROWIDS     |                              |       |       |            |          |       |       |
    |* 10 |          BITMAP INDEX SINGLE VALUE      | IDX_BUSIN_HOT_BITMAP_SEARCHS |       |       |            |          |   140 |   142 |
    |  11 |       TABLE ACCESS BY GLOBAL INDEX ROWID| BUSIN_SRH                    |     1 |  2019 |     4   (0)| 00:00:01 | ROW L | ROW L |
    |* 12 |        INDEX RANGE SCAN                 | ID_BUSIN_SRH_BC_ID1          |     1 |       |     3   (0)| 00:00:01 |       |       |
    |  13 |      TABLE ACCESS BY GLOBAL INDEX ROWID | PROVIDER_SRH                 |     1 |   258 |     2   (0)| 00:00:01 | ROW L | ROW L |
    |* 14 |       INDEX UNIQUE SCAN                 | PK166_1_1                    |     1 |       |     1   (0)| 00:00:01 |       |       |
    |* 15 |     TABLE ACCESS FULL                   | USER_AVERAGEQUALITYLOG       |     1 |    10 |  4342   (2)| 00:00:53 |       |       |
    |* 16 |   TABLE ACCESS BY INDEX ROWID           | KEYWORD_INFO_SRH             |     1 |    50 |     3   (0)| 00:00:01 |       |       |
    |* 17 |    INDEX RANGE SCAN                     | I_KEYINFOSRH_TONUM_INFOID    |     1 |       |     2   (0)| 00:00:01 |       |       |
    ----------------------------------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("BUS"."ACTID"="ACT"."ACTID")
       8 - filter(("SEARCHDATE">=TO_DATE(' 2015-07-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "LOADSTATES"='H' AND
                  "SEARCHDATE"<=TO_DATE(' 2015-09-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND "SRHCC"='001' AND "OPERSTATE"='1'))
      10 - access("SEARCHSTATE"='1')
      12 - access("BUSIN_HOT"."BC_ID"="BUS"."BC_ID")
      14 - access("BUS"."PROVIDERID"="COR"."PROVIDERID")
      15 - filter("COR"."PROVIDERID"="USRCOUNT"."PROVIDERID")
      16 - filter("KEYINFO"."INFOTYPE"='0')
      17 - access("BUS"."BC_ID"="KEYINFO"."SYS_NC00006$")

hint不要走位图索引的执行计划：

SELECT /*+ no_index(aa  IDX_BUSIN_HOT_BITMAP_SEARCHS) */ *
    -------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                               | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                        |                           |       |       | 73655 (100)|          |       |       |
    |   1 |  NESTED LOOPS OUTER                     |                           |    50 |   235K| 73655   (2)| 00:14:44 |       |       |
    |*  2 |   HASH JOIN RIGHT OUTER                 |                           |    50 |   232K| 73505   (2)| 00:14:43 |       |       |
    |   3 |    MAT_VIEW ACCESS FULL                 | BUSIN_ACTIVITY            |    24 |   336 |     3   (0)| 00:00:01 |       |       |
    |   4 |    NESTED LOOPS OUTER                   |                           |    10 | 23760 | 73502   (2)| 00:14:43 |       |       |
    |   5 |     NESTED LOOPS OUTER                  |                           |    10 | 23660 | 30077   (1)| 00:06:01 |       |       |
    |   6 |      NESTED LOOPS OUTER                 |                           |    10 | 21080 | 30057   (1)| 00:06:01 |       |       |
    |   7 |       PARTITION RANGE ITERATOR          |                           |    10 |   890 | 30017   (1)| 00:06:01 |   140 |   142 |
    |*  8 |        TABLE ACCESS FULL                | BUSIN_HOT                 |    10 |   890 | 30017   (1)| 00:06:01 |   140 |   142 |
    |   9 |       TABLE ACCESS BY GLOBAL INDEX ROWID| BUSIN_SRH                 |     1 |  2019 |     4   (0)| 00:00:01 | ROW L | ROW L |
    |* 10 |        INDEX RANGE SCAN                 | ID_BUSIN_SRH_BC_ID1       |     1 |       |     3   (0)| 00:00:01 |       |       |
    |  11 |      TABLE ACCESS BY GLOBAL INDEX ROWID | PROVIDER_SRH              |     1 |   258 |     2   (0)| 00:00:01 | ROW L | ROW L |
    |* 12 |       INDEX UNIQUE SCAN                 | PK166_1_1                 |     1 |       |     1   (0)| 00:00:01 |       |       |
    |* 13 |     TABLE ACCESS FULL                   | USER_AVERAGEQUALITYLOG    |     1 |    10 |  4342   (2)| 00:00:53 |       |       |
    |* 14 |   TABLE ACCESS BY INDEX ROWID           | KEYWORD_INFO_SRH          |     1 |    50 |     3   (0)| 00:00:01 |       |       |
    |* 15 |    INDEX RANGE SCAN                     | I_KEYINFOSRH_TONUM_INFOID |     1 |       |     2   (0)| 00:00:01 |       |       |
    -------------------------------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("BUS"."ACTID"="ACT"."ACTID")
       8 - filter(("SEARCHDATE">=TO_DATE(' 2015-07-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "LOADSTATES"='H' AND
                  "SEARCHSTATE"='1' AND "SEARCHDATE"<=TO_DATE(' 2015-09-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND "SRHCC"='001' AND
                  "OPERSTATE"='1'))
      10 - access("AA"."BC_ID"="BUS"."BC_ID")
      12 - access("BUS"."PROVIDERID"="COR"."PROVIDERID")
      13 - filter("COR"."PROVIDERID"="USRCOUNT"."PROVIDERID")
      14 - filter("KEYINFO"."INFOTYPE"='0')
      15 - access("BUS"."BC_ID"="KEYINFO"."SYS_NC00006$")

3分钟  
此表还有一个位图索引，配合使用的执行计划：

SELECT /*+ index(aa  IDX_BUSIN_HOT_BITMAP_SEARCHS IDX_BUSIN_HOT_BITMAP_OPERS) */ *
    ----------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                               | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                        |                              |       |       | 65393 (100)|          |       |       |
    |   1 |  NESTED LOOPS OUTER                     |                              |    50 |   235K| 65393   (2)| 00:13:05 |       |       |
    |*  2 |   HASH JOIN RIGHT OUTER                 |                              |    50 |   232K| 65243   (2)| 00:13:03 |       |       |
    |   3 |    MAT_VIEW ACCESS FULL                 | BUSIN_ACTIVITY               |    24 |   336 |     3   (0)| 00:00:01 |       |       |
    |   4 |    NESTED LOOPS OUTER                   |                              |    10 | 23760 | 65239   (2)| 00:13:03 |       |       |
    |   5 |     NESTED LOOPS OUTER                  |                              |    10 | 23660 | 21814   (1)| 00:04:22 |       |       |
    |   6 |      NESTED LOOPS OUTER                 |                              |    10 | 21080 | 21794   (1)| 00:04:22 |       |       |
    |   7 |       PARTITION RANGE ITERATOR          |                              |    10 |   890 | 21754   (1)| 00:04:22 |   140 |   142 |
    |*  8 |        TABLE ACCESS BY LOCAL INDEX ROWID| BUSIN_HOT                    |    10 |   890 | 21754   (1)| 00:04:22 |   140 |   142 |
    |   9 |         BITMAP CONVERSION TO ROWIDS     |                              |       |       |            |          |       |       |
    |  10 |          BITMAP AND                     |                              |       |       |            |          |       |       |
    |* 11 |           BITMAP INDEX SINGLE VALUE     | IDX_BUSIN_HOT_BITMAP_SEARCHS |       |       |            |          |   140 |   142 |
    |* 12 |           BITMAP INDEX SINGLE VALUE     | IDX_BUSIN_HOT_BITMAP_OPERS   |       |       |            |          |   140 |   142 |
    |  13 |       TABLE ACCESS BY GLOBAL INDEX ROWID| BUSIN_SRH                    |     1 |  2019 |     4   (0)| 00:00:01 | ROW L | ROW L |
    |* 14 |        INDEX RANGE SCAN                 | ID_BUSIN_SRH_BC_ID1          |     1 |       |     3   (0)| 00:00:01 |       |       |
    |  15 |      TABLE ACCESS BY GLOBAL INDEX ROWID | PROVIDER_SRH                 |     1 |   258 |     2   (0)| 00:00:01 | ROW L | ROW L |
    |* 16 |       INDEX UNIQUE SCAN                 | PK166_1_1                    |     1 |       |     1   (0)| 00:00:01 |       |       |
    |* 17 |     TABLE ACCESS FULL                   | USER_AVERAGEQUALITYLOG       |     1 |    10 |  4342   (2)| 00:00:53 |       |       |
    |* 18 |   TABLE ACCESS BY INDEX ROWID           | KEYWORD_INFO_SRH             |     1 |    50 |     3   (0)| 00:00:01 |       |       |
    |* 19 |    INDEX RANGE SCAN                     | I_KEYINFOSRH_TONUM_INFOID    |     1 |       |     2   (0)| 00:00:01 |       |       |
    ----------------------------------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("BUS"."ACTID"="ACT"."ACTID")
       8 - filter(("SEARCHDATE">=TO_DATE(' 2015-07-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "LOADSTATES"='H' AND
                  "SEARCHDATE"<=TO_DATE(' 2015-09-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND "SRHCC"='001'))
      11 - access("SEARCHSTATE"='1')
      12 - access("OPERSTATE"='1')
      14 - access("AA"."BC_ID"="BUS"."BC_ID")
      16 - access("BUS"."PROVIDERID"="COR"."PROVIDERID")
      17 - filter("COR"."PROVIDERID"="USRCOUNT"."PROVIDERID")
      18 - filter("KEYINFO"."INFOTYPE"='0')
      19 - access("BUS"."BC_ID"="KEYINFO"."SYS_NC00006$")

比上面不走位图索引略快

生产上使用位图索引要慎重！！！