oracle with sql优化,利用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/，如需转载，请注明出处，否则将追究法律责任。