快过年了，要保证库的稳定，以免影响假期，早上来就跑了个一周的AWR，发现TOP1的sql很诡异
/* Formatted on 2012-1-12 13:43:28 (QP5 v5.149.1003.31008) */
SELECT *
FROM app.map p LEFT JOIN APP.PG m ON devid = p.loggerid
WHERE (devid = 'YQ6101250003'
AND (curdatetime BETWEEN TO_DATE ('2011-05-01 00', 'YYYY-MM-DD HH24')
AND TO_DATE ('2011-05-05 00', 'YYYY-MM-DD HH24')))
OR (devid = 'YQ6101250004'
AND (curdatetime BETWEEN TO_DATE ('2011-05-01 00',
'YYYY-MM-DD HH24')
AND TO_DATE ('2011-05-05 00',
'YYYY-MM-DD HH24')))
OR (devid = 'YQ6101250005'
AND (curdatetime BETWEEN TO_DATE ('2011-05-01 00',
'YYYY-MM-DD HH24')
AND TO_DATE ('2011-05-05 00',
'YYYY-MM-DD HH24')))
OR (devid = 'YQ6101250002'
AND (curdatetime BETWEEN TO_DATE ('2011-05-01 00',
'YYYY-MM-DD HH24')
AND TO_DATE ('2011-05-05 00',
'YYYY-MM-DD HH24')))
ORDER BY devid, curdatetime;
手工跑了一下，好家伙18分45秒，这里使用了大量的or，这会使查询速度相当的慢，可以改用union all
手工跑了一下，好家伙18分45秒，这里使用了大量的or，这会使查询速度相当的慢，可以改用union all
/* Formatted on 2012-1-12 14:15:00 (QP5 v5.149.1003.31008) */
SELECT *
FROM app.map p
LEFT JOIN
app.PG m
ON devid = p.loggerid
WHERE devid = 'YQ6101250003'
AND curdatetime BETWEEN TO_DATE ('2011-05-01 00', 'YYYY-MM-DD HH24')
AND TO_DATE ('2011-05-05 00', 'YYYY-MM-DD HH24')
UNION ALL
SELECT *
FROM app.map p
LEFT JOIN
app.PG m
ON devid = p.loggerid
WHERE devid = 'YQ6101250004' AND curdatetime BETWEEN TO_DATE ('2011-05-01 00', 'YYYY-MM-DD HH24') AND TO_DATE ('2011-05-05 00', 'YYYY-MM-DD HH24')
UNION ALL
SELECT *
FROM app.map p
LEFT JOIN
app.PG m
ON devid = p.loggerid
WHERE devid = 'YQ6101250005'
AND curdatetime BETWEEN TO_DATE ('2011-05-01 00', 'YYYY-MM-DD HH24')
AND TO_DATE ('2011-05-05 00', 'YYYY-MM-DD HH24')
UNION ALL
SELECT *
FROM app.map p
LEFT JOIN
app.PG m
ON devid = p.loggerid
WHERE devid = 'YQ6101250002'
AND curdatetime BETWEEN TO_DATE ('2011-05-01 00', 'YYYY-MM-DD HH24')
AND TO_DATE ('2011-05-05 00', 'YYYY-MM-DD HH24')
再次查询，2秒，哈哈就喜欢这种优化后这种几何级增长的。联系了下开发人员，原来是在代码中拼SQL的，or的个数未知，看来要改代码了，都改成union all吧
当然这个例子也可以使用in来解决
当然这个例子也可以使用in来解决
/* Formatted on 2012-1-12 13:43:28 (QP5 v5.149.1003.31008) */
SELECT *
FROM app.map p LEFT JOIN app.PG_HH m ON devid = p.loggerid
WHERE devid in( 'YQ6101250003', 'YQ6101250004','YQ6101250005','YQ6101250002')
AND curdatetime BETWEEN TO_DATE ('2011-05-01 00',
'YYYY-MM-DD HH24')
AND TO_DATE ('2011-05-05 00',
'YYYY-MM-DD HH24')
ORDER BY devid, curdatetime
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25680865/viewspace-714819/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
RAC下使用pfile创建spfile
下一篇：
跨年问题，创建spfile遇见bug
请登录后发表评论
登录
全部评论
