利用with as 优化sql

原sql：
--select * from table(dbms_xplan.display_cursor('bzk723y7fcwdb'));
select /*+gather_plan_statistics */count(haa.id) as count,
       nvl(sum(terminalBindCount), 0) as terminalBindCount,
       nvl(sum(merchantBindCount), 0) as merchantBindCount
  from qpos.hkrt_ams_agent haa
  left join (select a.id as id,
                    (select count(*)
                       from qpos.hkrt_ams_terminal t
                       left join qpos.hkrt_ams_system_terminal st
                         on t.system_terminal_id = st.id
                       left join qpos.hkrt_ams_merchant mer
                         on st.merchant_id = mer.id
                      where t.agent_id = a.id
                        and t.terminal_status = '10A'
                        and st.merchant_id is not null) as merchantBindCount,
                    (select count(*)
                       from qpos.hkrt_ams_terminal t
                      where t.agent_id = a.id
                        and t.terminal_status = '10A') as terminalBindCount
               from qpos.hkrt_ams_agent a) t4
    on t4.id = haa.id
  left join qpos.hkrt_system_china_area hsa
    on haa.china_area = hsa.id
  left join qpos.hkrt_ams_agent a
    on haa.father_agent_id = a.id
 where 1 = 1
   and haa.father_agent_id is not null
   and haa.agent_type = '10A'
   and haa.service_manager_id = '1358E50F98244A3884D8B71F2BFC99C4';


更改之后的sql：
WITH t4 AS
(SELECT 
   a.id AS id,
   (SELECT COUNT(*)
      FROM hkrt_ams_terminal t
      LEFT JOIN hkrt_ams_system_terminal st
        ON t.system_terminal_id = st.id
      LEFT JOIN hkrt_ams_merchant mer
        ON st.merchant_id = mer.id
     WHERE t.agent_id = a.id
       AND t.terminal_status = '10A'
       AND st.merchant_id IS NOT NULL) AS merchantBindCount,
   (SELECT COUNT(*)
      FROM hkrt_ams_terminal t
     WHERE t.agent_id = a.id
       AND t.terminal_status = '10A') AS terminalBindCount
    FROM hkrt_ams_agent a
   where a.father_agent_id IS NOT NULL
     AND a.agent_type = '10A' 
     AND a.service_manager_id = '1358E50F98244A3884D8B71F2BFC99C4')
     
     SELECT COUNT(haa.id) AS COUNT,
       NVL(SUM(terminalBindCount), 0) AS terminalBindCount,
       NVL(SUM(merchantBindCount), 0) AS merchantBindCount
  FROM hkrt_ams_agent haa
  LEFT JOIN t4
    ON t4.id = haa.id
  LEFT JOIN hkrt_system_china_area hsa
    ON haa.china_area = hsa.id
  LEFT JOIN hkrt_ams_agent a
    ON haa.father_agent_id = a.id
 WHERE 1 = 1
   AND haa.father_agent_id IS NOT NULL
   AND haa.agent_type = '10A'
   AND haa.service_manager_id = '1358E50F98244A3884D8B71F2BFC99C4'

--上面主要就是标量子查询的优化，标量子查询和普通子查询的区别就是，标量子查询执行多次，普通子查询只需要执行一次。通过上面的改法，可以有效的避免标量子查询。使用with as 将构造表
弄成一个类似临时表的功能。一般标量子查询适用于查询数据量小的子查询，感谢黄老师的指导！谢谢！

例:
–针对一个别名
with tmp as (select * from tb_name)


–针对多个别名
with
   tmp as (select * from tb_name),
   tmp2 as (select * from tb_name2),
   tmp3 as (select * from tb_name3),




  案例：
  with a3 as
 (select c2
    from b, c
   where b.a1 = a.a1
     and b.b2 = c.b2
     and rownum = 1),
a4 as
 (select b2
    from b
   where b.a1 = a.a1
     and rownum = 1),
a5 as
 (select c2
    from c
   where c.a1 = a.a1
     and rownum = 1)
select a2, a3, a4, a5 from a;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30536096/viewspace-1990480/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30536096/viewspace-1990480/