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

Question

本帖最后由 wangkai88 于 2012-08-17 23:06:02 编辑

Answer 1

--看下是不是这个更久呀，呵呵 
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'

Answer 2

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, //这个地方提示为找到from关键字
                                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'

Answer 3

select a.cjyh,--少个逗号
count(1) wfzp,

Answer 4

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'

Answer 5

我的哥哥，你几乎都是再一张表上做操作wpw_users ，怎么出来这么多子查询啊，合并先速度就上去了

Answer 6

-- 你看我理解有没有错，是否跟你的等效。
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 yxsl
from wpw_users z
where rownum < 21
and 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;

Answer 7

哥们这语句没起到一点优化的效果啊，这SQL问题不是在分页上面把

Answer 8

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')

Answer 9

OK 了 找到问题了 
在用户字段上面创建两个索引就好了create index IDX_T_TMS_PECCANCY_CJYH on T_TMS_PECCANCY (CJYH);
create index IDX_WPW_QUERY_ZGCX_RID on WPW_QUERY_ZGCX (RID);非常感谢上面几位大侠们的支持