大家好，今天开发部送来了一个需求
一个表供
SELECT COUNT(*) FROM a1QUA ；----56875
那边写的sql是：
select (SELECT COUNT(*) FROM a1qua
WHERE r1 = '211' AND l_test = 'a1_a1type_0'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '211 AND l_test = 'a1_a1type_1'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '212' AND l_test = 'a1_a1type_0'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '212' AND l_test = 'a1_a1type_1'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '213' AND l_test = 'a1_a1type_0'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '213' AND l_test = 'a1_a1type_1'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '214' AND l_test = 'a1_a1type_0'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '214' AND l_test = 'a1_a1type_1'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '215' AND l_test = 'a1_a1type_0'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '215' AND l_test = 'a1_a1type_1'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '216' AND l_test = 'a1_a1type_0'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '216' AND l_test = 'a1_a1type_1'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '217' AND l_test = 'a1_a1type_0'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '217' AND l_test = 'a1_a1type_1'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '218' AND l_test = 'a1_a1type_0'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '218' AND l_test = 'a1_a1type_1'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '219' AND l_test = 'a1_a1type_0'),
(SELECT COUNT(*) FROM a1qua
WHERE r1 = '219' AND l_test = 'a1_a1type_1')
from dual; ---用时1.5S
后来我该了一下，sql改成了
select max(a) , max(b) ,
max(c) , max(d) ,
max(e) , max(f) ,
max(g) , max(h) ,
max(i) , max(j) ,
max(k) , max(l) ,
max(m) , max(n) ,
max(o) , max(p) ,
max(q) , max(r)
from (select count(decode(r1,
'211',
(decode(l_test,
'a1_a1type_0',
'1')))) as a,
count(decode(r1,
'211',
(decode(l_test,
'a1_a1type_1',
'1')))) as b,
count(decode(r1,
'212',
(decode(l_test,
'a1_a1type_0',
'1')))) as c,
count(decode(r1,
'212',
(decode(l_test,
'a1_a1type_1',
'1')))) as d,
count(decode(r1,
'213',
(decode(l_test,
'a1_a1type_0',
'1')))) as e,
count(decode(r1,
'213',
(decode(l_test,
'a1_a1type_1',
'1')))) as f,
count(decode(r1,
'214',
(decode(l_test,
'a1_a1type_0',
'1')))) as g,
count(decode(r1,
'214',
(decode(l_test,
'a1_a1type_1',
'1')))) as h,
count(decode(r1,
'215',
(decode(l_test,
'a1_a1type_0',
'1')))) as i,
count(decode(r1,
'215',
(decode(l_test,
'a1_a1type_1',
'1')))) as j,
count(decode(r1,
'216',
(decode(l_test,
'a1_a1type_0',
'1')))) as k,
count(decode(r1,
'216',
(decode(l_test,
'a1_a1type_1',
'1')))) as l,
count(decode(r1,
'217',
(decode(l_test,
'a1_a1type_0',
'1')))) as m,
count(decode(r1,
'217',
(decode(l_test,
'a1_a1type_1',
'1')))) as n,
count(decode(r1,
'218',
(decode(l_test,
'a1_a1type_0',
'1')))) as o,
count(decode(r1,
'218',
(decode(l_test,
'a1_a1type_1',
'1')))) as p,
count(decode(r1,
'219',
(decode(l_test,
'a1_a1type_0',
'1')))) as q,
count(decode(r1,
'219',
(decode(l_test,
'a1_a1type_1',
'1')))) as r
from a1qua
group by r1, l_test); ---用时0.687s
尽量一次能够查询出来，但是效率还不是很高在0.678s左右徘徊，
r1,l_test都不是索引，
大家帮忙看看还能优化么？
阅读(3036) | 评论(1) | 转发(0) |
