group by 没用索引 速度很慢
SELECT a.id,
a.file,
a.date,
COUNT(DECODE(a.status, 'C', 'X', NULL)) C_COUNT,
COUNT(DECODE(a.status, 'B', 'X', NULL)) B_COUNT,
COUNT(DECODE(a.status, 'V', 'X', NULL)) V_COUNT
FROM tablea a,
tableb b
WHERE 1 = 1
AND a.id= b.id
GROUP BY a.id,
a.file,
a.date
上面的语句 a.id, a.file, a.date都是索引,两个表的数据量都在百万
执行计划:
SELECT STATEMENT, GOAL = CHOOSE 11861 945 29295
SORT GROUP BY 11861 945 9295
HASH JOIN 5316 1583041 49074271
TABLE ACCESS FULL tablea 2061 456765 6851475
TABLE ACCESS FULL tableb 1866 1583041 25328656
目前的执行速度很慢,哪位帮忙看看,怎么样才能用上索引,或者能提高速度,小弟先谢谢了