求解下面SQL优化

Question

SELECT WK.HANDLINGSTAFF, COUNT(WK.SERIALNO) CNT
  FROM T_PBH_PROBLEMWORKITEMHIS WK, T_PBH_PROBLEMPROCESSHIS PP
 WHERE WK.SERIALNO = PP.SERIALNO
   AND WK.HANDLINGROLE = '20090113165355'
   AND WK.OPERATETYPE = '0005'
   AND PP.ACCEPTTIME >= TO_DATE('2010-08-30', 'YYYY-MM-DD')
   AND PP.ACCEPTTIME < TO_DATE('2010-08-30', 'YYYY-MM-DD') + 1
 GROUP BY WK.HANDLINGSTAFF;上面的SQL如何优化？两张表的数据都是200W左右，查询很慢。看了执行计划，都走了索引。

Answer 1

WK.HANDLINGSTAFF 是什么类型，是否有索引，什么类型的索引，数据分布如何（重复的多？）

Answer 2

SELECT WK.HANDLINGSTAFF, COUNT(WK.SERIALNO) CNT from (select HANDLINGSTAFF, SERIALNO CNT from T_PBH_PROBLEMWORKITEMHIS) WK, T_PBH_PROBLEMPROCESSHIS PP
 WHERE PP.ACCEPTTIME >= TO_DATE('2010-08-30', 'YYYY-MM-DD')
  AND PP.ACCEPTTIME < TO_DATE('2010-08-30', 'YYYY-MM-DD') + 1
  AND WK.SERIALNO = PP.SERIALNO
  AND WK.HANDLINGROLE = '20090113165355'
  AND WK.OPERATETYPE = '0005'
 GROUP BY WK.HANDLINGSTAFF;

Answer 3

SELECT STATEMENT, GOAL = CHOOSE 19 19 593 52777 BIOP0224 SELECT STATEMENT CHOOSE 2011-3-9 9:59:09
 SORT GROUP BY 19 19 593 52777 BIOP0224 SORT GROUP BY 2011-3-9 9:59:09
  NESTED LOOPS 13 13 593 52777 BIOP0224 NESTED LOOPS 2011-3-9 9:59:09
   TABLE ACCESS BY GLOBAL INDEX ROWID 2 2 XW12345 T_PBH_PROBLEMWORKITEMHIS 592 28416 "WK"."OPERATETYPE"='0005' BIOP0224 1 TABLE ACCESS BY GLOBAL INDEX ROWID ROW LOCATION ROW LOCATION 2011-3-9 9:59:09
    INDEX RANGE SCAN 1 1 XW12345 IX_PBH_WORKITEM_HLR 23664 "WK"."HANDLINGROLE"='20090113165355' BIOP0224 NON-UNIQUE INDEX RANGE SCAN 1 2011-3-9 9:59:09
   INDEX FAST FULL SCAN 4 4 XW12345 PK_T_PBH_PROBLEMPROCESSHIS 1 41 "WK"."SERIALNO"="PP"."SERIALNO" AND "PP"."ACCEPTTIME">=TO_DATE('2010-08-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "PP"."ACCEPTTIME"<TO_DATE('2010-08-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') BIOP0224 UNIQUE INDEX FAST FULL SCAN 2011-3-9 9:59:09

Answer 4

楼主试下利用function
不同时扫描两表
在pp 表的serialno 创建索引
SELECT WK.HANDLINGSTAFF, function_1(WK.SERIALNO) CNT
  FROM T_PBH_PROBLEMWORKITEMHIS WK, T_PBH_PROBLEMPROCESSHIS PP
 WHERE 
  WK.HANDLINGROLE = '20090113165355'
  AND WK.OPERATETYPE = '0005'
 
 GROUP BY WK.HANDLINGSTAFF;function1(wk.serialno array) return countserialno
countserialno :=0;
tempcount :=0;
for i in array
loop select count(*)into tempcount from T_PBH_PROBLEMPROCESSHIS pp
where PP.ACCEPTTIME >= TO_DATE('2010-08-30', 'YYYY-MM-DD')
  AND PP.ACCEPTTIME < TO_DATE('2010-08-30', 'YYYY-MM-DD') + 1
  and pp.serialno=array[i]
  countserialno := countserialno +tempcount;
return countserialno;

Answer 5

不管用啊，SELECT WK.HANDLINGSTAFF, function_1(WK.SERIALNO) CNT
提示参数类型不正确，你传入的是varchar2

Answer 6

SELECT WK.HANDLINGSTAFF, COUNT(WK.SERIALNO) CNT
  FROM T_PBH_PROBLEMWORKITEMHIS WK,
( SELECT ACCEPTTIME,SERIALNO  FROM T_PBH_PROBLEMPROCESSHIS P  
 WHERE  P.ACCEPTTIME >= TO_DATE('2010-08-30', 'YYYY-MM-DD')
  AND P.ACCEPTTIME < TO_DATE('2010-08-30', 'YYYY-MM-DD') + 1 
)PP
 WHERE WK.SERIALNO = PP.SERIALNO
  AND WK.HANDLINGROLE = '20090113165355'
  AND WK.OPERATETYPE = '0005' GROUP BY WK.HANDLINGSTAFF;

Answer 7

再或者:SELECT WK.HANDLINGSTAFF, COUNT(WK.SERIALNO) CNT
  FROM
 ( SELECT K.HANDLINGSTAFF, K.SERIALNO
   FROM  T_PBH_PROBLEMWORKITEMHIS
   K WHERE    K.HANDLINGROLE = '20090113165355'
       AND K.OPERATETYPE = '0005'
  ) WK,
( SELECT ACCEPTTIME,SERIALNO FROM T_PBH_PROBLEMPROCESSHIS P  
 WHERE P.ACCEPTTIME >= TO_DATE('2010-08-30', 'YYYY-MM-DD')
  AND P.ACCEPTTIME < TO_DATE('2010-08-30', 'YYYY-MM-DD') + 1 
)PP
 WHERE WK.SERIALNO = PP.SERIALNO GROUP BY WK.HANDLINGSTAFF;請 試試 

Answer 8

1. 根据查询条件，并看数据分布情况在表上建INDEX
2. 并发全表扫描
SELECT /*+ full(wk) parallel(wk default) */WK.HANDLINGSTAFF, COUNT(WK.SERIALNO) CNT
  FROM T_PBH_PROBLEMWORKITEMHIS WK, T_PBH_PROBLEMPROCESSHIS PP
 WHERE WK.SERIALNO = PP.SERIALNO
  AND WK.HANDLINGROLE = '20090113165355'
  AND WK.OPERATETYPE = '0005'
  AND PP.ACCEPTTIME >= TO_DATE('2010-08-30', 'YYYY-MM-DD')
  AND PP.ACCEPTTIME < TO_DATE('2010-08-30', 'YYYY-MM-DD') + 1
 GROUP BY WK.HANDLINGSTAFF;

Answer 9

1、先在关联字段是建立索引；
2、用子查询语句，先将结果集缩小后再进行关联查询SELECT WK.HANDLINGSTAFF, COUNT(WK.SERIALNO) CNT
  FROM 
 ( select a.HANDLINGSTAFF,a.SERIALNO from  T_PBH_PROBLEMWORKITEMHIS a
 where a.HANDLINGROLE = '20090113165355'
  AND a.OPERATETYPE = '0005' ) WK
, 
 (select b.SERIALNO from T_PBH_PROBLEMPROCESSHIS b
  where b.ACCEPTTIME >= TO_DATE('2010-08-30', 'YYYY-MM-DD')
  AND b.ACCEPTTIME < TO_DATE('2010-08-30', 'YYYY-MM-DD') + 1
 ) PP
 WHERE WK.SERIALNO = PP.SERIALNO
 GROUP BY WK.HANDLINGSTAFF;