--先过滤在连接看看 然后在建索引 --create index idx on table1(pno)SELECT a.managecom 机构代码, a.sx 销售渠道, COUNT(distinct a.appno) 申请人数量, SUM(functiona( b.pno, b.riskcode, b.paymoney, b.dutycode )) 累计标准保费 FROM table1 a, table2 b WHERE AND a.sx IN (1, 4) AND b.paymode = 1 AND a.cdate <= date'2009-11-06' and a.pno = b.pno AND EXISTS (SELECT 'X' FROM table3 t WHERE t.stype = 'Enable' AND t.state = 0 AND t.enddate IS NULL and t.pno = b.pno AND t.startdate <= (SELECT MAX(t1.startdate) FROM table3 t1 WHERE t1.startdate <= date '2009-11-06')) GROUP BY a.managecom, a.sx
1. 看你SQL的写法,建议你把 AND EXISTS (SELECT 'X' FROM table3 t WHERE t.stype = 'Enable' AND t.state = 0 AND t.enddate IS NULL and t.pno = b.pno改写为FROM table1 a, table2 b, table3 t where a.pno = b.pno and t.pno = b.pno and ... (其他条件照抄)2. 因为没有数据,也没有具体业务逻辑,无法判断表数据量大小,请贴出执行计划以便进一步调优。
2.在被探查的表上的关联字段建立索引,如果管理字段唯一建立唯一索引,如果关联字段唯一并且是序列建立反序索引。)【1.pno=b.pon】
3.如果字段取值小于总记录的1%,建立位图索引。
--create index idx on table1(pno)SELECT a.managecom 机构代码,
a.sx 销售渠道,
COUNT(distinct a.appno) 申请人数量,
SUM(functiona( b.pno,
b.riskcode,
b.paymoney,
b.dutycode
)) 累计标准保费
FROM table1 a, table2 b
WHERE AND a.sx IN (1, 4)
AND b.paymode = 1
AND a.cdate <= date'2009-11-06'
and a.pno = b.pno
AND EXISTS
(SELECT 'X'
FROM table3 t
WHERE t.stype = 'Enable'
AND t.state = 0
AND t.enddate IS NULL and t.pno = b.pno AND t.startdate <=
(SELECT MAX(t1.startdate)
FROM table3 t1
WHERE t1.startdate <= date '2009-11-06'))
GROUP BY a.managecom, a.sx
AND EXISTS
(SELECT 'X'
FROM table3 t
WHERE t.stype = 'Enable'
AND t.state = 0
AND t.enddate IS NULL and t.pno = b.pno改写为FROM table1 a, table2 b, table3 t
where a.pno = b.pno
and t.pno = b.pno
and ... (其他条件照抄)2. 因为没有数据,也没有具体业务逻辑,无法判断表数据量大小,请贴出执行计划以便进一步调优。
EXISTS 这个子查询好像没法关联了,因为一个关联条件下,子查询返回了多个记录,这样就有重复记录。
7万多的时候都是全表扫描