求教:这个语句怎么优化
(1)SELECT A.*, (SELECT COUNT(C.ID) FROM C WHERE C.ID = A.ID) AS CONT1
FROM A
ORDER BY A.JKRQ
第(1)句在plsql上执行,显示时间是3.8s;
(2)SELECT A.*
FROM A
ORDER BY A.JKRQ
如果是只执行(2)的话,只要0.15s;
(3)SELECT A.*, (SELECT COUNT(C.ID) FROM C WHERE C.ID = A.ID) AS CONT1
FROM A
执行第(3)句的话是0.2s。
也就是说,如果只查询A表并排序和只做一个外表count不排序的时间都不多,但是2者合并起来的话,查询就慢了,(jkrq在A表上建有索引)。
那这第(1)个语句该怎么优化呢?
------解决方案--------------------
SELECT A.*, B.total
FROM A, (select id, count(id) as total from c group by id) B
where A.id = B.id
ORDER BY A.JKRQ