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

发布时间：2020-12-12 16:21:38 所属栏目：百科来源：网络整理

导读：原来系统中运行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

原来系统中运行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,'') AS jj,
REPLACE (cor.srhintro,'') 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 "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$")

比上面不走位图索引略快

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!