求解下面SQL优化

layo 2011-03-07 03:35:06

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左右，查询很慢。看了执行计划，都走了索引。

...全文

143 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

kezhiy 2011-03-10

打赏
举报

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;

uooooppp 2011-03-09

打赏
举报

楼主试下利用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;

minitoy 2011-03-09

打赏
举报

可以试试子查询，将选择性较好的表写成子查询，先过滤表间连接记录的数量

layo 2011-03-09

打赏
举报

[Quote=引用 3 楼 zty598416146 的回复:]
走不走索引是不一回事，关键是看索引是怎么走的，发执行计划。
[/Quote]

大家不好意思，这两天面试去了，没上班发执行计划。上面楼是执行计划。

layo 2011-03-09

打赏
举报

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

kakaxi 2011-03-09

打赏
举报

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;

Samrv.W 2011-03-09

打赏
举报

再或者:

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;

請試試

Samrv.W 2011-03-09

打赏
举报

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;

layo 2011-03-09

打赏
举报

不管用啊，SELECT WK.HANDLINGSTAFF, function_1(WK.SERIALNO) CNT
提示参数类型不正确，你传入的是varchar2。。。
[Quote=引用 8 楼 uooooppp 的回复:]
楼主试下利用function
不同时扫描两表
在pp 表的serialno 创建索引
SELECT WK.HANDLINGSTAFF, function_1(WK.SERIALNO) CNT
FROM T_PBH_PROBLEMWORKITEMHIS WK, T_PBH_PROBLEMPROCESSHIS PP
WHERE
WK.HANDLINGROLE = '200901……
[/Quote]

lxyzxq2008 2011-03-07

打赏
举报



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;