SQL优化经典----利用分析函数优化自连接

SQL优化经典----利用分析函数优化自连接

 

----http://blog.sina.com.cn/s/blog_61cd89f60102efak.html 

   

这几天看到一个不错的SQL优化案例，读完感觉很受启发，在此记录一下优化思路了具体分析过程，对于SQL优化我们的目标是减少逻辑读、减少递归调用，我们来看一下

select distinct decode(length(a.category_id), 
                       5, 
                       decode(a.origin_type, 801, 888888, 999999), 
                       a.category_id) category_id, 
                a.notice_code, 
                a.treat_status, 
                lr.real_name as receiver_name, 
                f.send_code, 
                f.policy_code, 
                g.real_name agent_name, 
                f.organ_id, 
                f.dept_id, 
                a.policy_id, 
                a.change_id, 
                a.case_id, 
                a.group_policy_id, 
                a.fee_id, 
                a.auth_id, 
                a.pay_id, 
                cancel_appoint.appoint_time cancel_appoint_time, 
                a.insert_time, 
                a.send_time, 
                a.end_time, 
                f.agency_code, 
                a.REPLY_TIME, 
                a.REPLY_EMP_ID, 
                a.FIRST_DUTY, 
                a.NEED_SEND_PRINT, 
                11 source 
  from t_policy_problem        a, 
       t_policy                f, 
       t_agent                 g, 
       t_letter_receiver       lr, 
       t_problem_category      pc, 
       t_policy_cancel_appoint cancel_appoint 
 where f.agent_id = g.agent_id(+) 
   and a.policy_id = f.policy_id(+) 
   and lr.main_receiver = 'Y' 
   and a.category_id = pc.category_id 
   and a.item_id = lr.item_id 
   and a.policy_id = cancel_appoint.policy_id(+) 
   And a.Item_Id = (Select Max(item_id) 
                      From t_Policy_Problem 
                     Where notice_code = a.notice_code) 
   and a.policy_id is not null 
   and a.notice_code is not null 
   and a.change_id is null 
   and a.case_id is null 
   and a.group_policy_id is null 
   and a.origin_type not in (801, 802) 
   and a.pay_id is null 
   and a.category_id not in 
       (130103, 130104, 130102, 140102, 140101) 
   and f.policy_type = 1 
   and (a.fee_id is null or 
       (a.fee_id is not null and a.origin_type = 701)) 
   and f.organ_id in 
       (select distinct organ_id 
          from T_COMPANY_ORGAN 
         start with organ_id = '107' 
        connect by parent_id = prior organ_id) 
   and pc.NEED_PRITN = 'Y'

优化后：

WITH t_Policy_Problem_w AS
 (SELECT tp.*, max(item_id) OVER(PARTITION BY notice_code) max_item_id
    FROM t_Policy_Problem tp)
select distinct decode(length(a.category_id),
                       5,
                       decode(a.origin_type, 801, 888888, 999999),
                       a.category_id) category_id,
                a.notice_code,
                a.treat_status,
                lr.real_name as receiver_name,
                f.send_code,
                f.policy_code,
                g.real_name agent_name,
                f.organ_id,
                f.dept_id,
                a.policy_id,
                a.change_id,
                a.case_id,
                a.group_policy_id,
                a.fee_id,
                a.auth_id,
                a.pay_id,
                cancel_appoint.appoint_time cancel_appoint_time,
                a.insert_time,
                a.send_time,
                a.end_time,
                f.agency_code,
                a.REPLY_TIME,
                a.REPLY_EMP_ID,
                a.FIRST_DUTY,
                a.NEED_SEND_PRINT,
                11 source
  from t_Policy_Problem_w      a,
       t_policy                f,
       t_agent                 g,
       t_letter_receiver       lr,
       t_problem_category      pc,
       t_policy_cancel_appoint cancel_appoint
 where a.item_id = a.max_item_id
   and f.agent_id = g.agent_id(+)
   and a.policy_id = f.policy_id(+)
   and lr.main_receiver = 'Y'
   and a.category_id = pc.category_id
   and a.item_id = lr.item_id
   and a.policy_id = cancel_appoint.policy_id(+)
   and a.policy_id is not null
   and a.notice_code is not null
   and a.change_id is null
   and a.case_id is null
   and a.group_policy_id is null
   and a.origin_type not in (801, 802)
   and a.pay_id is null
   and a.category_id not in (130103, 130104, 130102, 140102, 140101)
   and f.policy_type = 1
   and (a.fee_id is null or (a.fee_id is not null and a.origin_type = 701))
   and f.organ_id in (select distinct organ_id
                        from T_COMPANY_ORGAN
                       start with organ_id = '107'
                      connect by parent_id = prior organ_id)
   and pc.NEED_PRITN = 'Y'
 利用分析函数max() over(partition by )优化自连接，具体分析详见：

http://blog.csdn.net/robinson1988/article/details/7219958