求一条SQL语句优化，一张300M的表查3分钟
oracle有一张表300M 关键字段的索引也建了查询还是很慢，50M的时候很快t_tms_peccancy 这张表的数据越多就越慢了，求高手优化一下SQL语句
SELECT * FROM (SELECT A.*, ROWNUM RN FROM (select (select user_name from wpw_users where login_name = z.login_name) as name, (select count(*) from t_tms_peccancy a, wpw_users b where a.cjyh = b.login_name and a.cjyh = z.login_name and a.sjly = '7' and a.cjsj > to_date('2011-07-07 00:00', 'yyyy-mm-dd hh24:mi:ss') and a.cjsj < to_date('2012-08-17 21:12', 'yyyy-mm-dd hh24:mi:ss')) as wfzp, (select count(*) from t_tms_peccancy a, wpw_users b where a.cjyh = b.login_name and a.cjyh = z.login_name and a.sjly = '7' and shbj = '2' and a.cjsj > to_date('2011-07-07 00:00', 'yyyy-mm-dd hh24:mi:ss') and a.cjsj < to_date('2012-08-17 21:12', 'yyyy-mm-dd hh24:mi:ss')) as yxsl from wpw_users z where z.login_name in (select login_name from wpw_users u join wpw_dept on u.work_part_id = wpw_dept.id where work_part_id = '371722000000') order by z.login_name desc) A) where RN between '1' and '20'--看下是不是这个更久呀，呵呵 SELECT * FROM (SELECT A.*, ROWNUM RN FROM (select (select user_name from wpw_users where login_name = z.login_name) as name, t.wfzp, t.yxsl from wpw_users z left join (select a.cjyh count(1) wfzp, count(decode(shbj,'2',null,1)) yxsl from t_tms_peccancy a, wpw_users b where a.cjyh = b.login_name and a.cjyh = z.login_name and a.sjly = '7' and a.cjsj > to_date('2011-07-07 00:00', 'yyyy-mm-dd hh24:mi:ss') and a.cjsj < to_date('2012-08-17 21:12', 'yyyy-mm-dd hh24:mi:ss') group by a.cjyh) t where z.login_name=t.cjyh and exists (select 1 from wpw_users u join wpw_dept on u.work_part_id = wpw_dept.id where z.login_name=u.login_name and work_part_id = '371722000000') order by z.login_name desc) A) where RN between '1' and '20'
[解决办法]
-- 你看我理解有没有错，是否跟你的等效。select (select user_name from wpw_users where login_name = z.login_name) as name,(select count(*) from t_tms_peccancy a, wpw_users b where a.cjyh = z.login_name and a.cjyh = b.login_name and a.sjly = '7' and a.cjsj > to_date('2011-07-07 00:00', 'yyyy-mm-dd hh24:mi:ss') and a.cjsj < to_date('2012-08-17 21:12', 'yyyy-mm-dd hh24:mi:ss')) as wfzp,(select count(*) from t_tms_peccancy a, wpw_users b where a.cjyh = z.login_name and a.cjyh = b.login_name and a.sjly = '7' and shbj = '2' and a.cjsj > to_date('2011-07-07 00:00', 'yyyy-mm-dd hh24:mi:ss') and a.cjsj < to_date('2012-08-17 21:12', 'yyyy-mm-dd hh24:mi:ss')) as yxslfrom wpw_users zwhere rownum < 21and exists (select 1 from wpw_dept v where z.work_part_id = v.id)and work_part_id = '371722000000'order by z.login_name desc;