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
目前的执行速度很慢,哪位帮忙看看,怎么样才能用上索引,或者能提高速度,小弟先谢谢了
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
目前的执行速度很慢,哪位帮忙看看,怎么样才能用上索引,或者能提高速度,小弟先谢谢了
从执行计划看,没有走索引而是全表扫描,试试hint 强制索引SELECT /*+ index(table_name,index_name) */a.id,
/*+ index(table_name,index_name) */a.file,
/*+ index(table_name,index_name) */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 ------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716
这样做:
1.tablea上建联合索引:create index xx on tablea(id,file,date);
2.tableb上建索引:create index xx2 on tableb(id);再执行下,把执行计划贴出来,正确的话两个表都可以走INDEX FAST FULL SCAN
顶
2.tableb上建索引:create index xx2 on tableb(id);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
inner join tableb b on a.id= b.id
WHERE 1 = 1
GROUP BY a.id,
a.file,
a.date