求一条SQL话语优化，一张300M的表查3分钟

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'
------解决方案--------------------
SQL code
-- 你看我理解有没有错，是否跟你的等效。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;