oracle使用with as提高查询效率

经常在开发过程中会用到视图或组合查询的情况，但由于涉及表数据经常达到千万级别的笛卡尔积，而且一段查询时会反复调用，但结果输出往往不需要那么多，可以使用with将过滤或处理后的结果先缓存到临时表（此处原理不太清楚，仅代表我的理解），可以大大提高查询效率

另外，WMSYS.WM_CONCAT 这个函数可以将查询结果某列多行拼接成一个结果，非常实用

示例：

/*
查询当前EOMS流程组及组织架构信息
create by Gemini.Liu
2014-01-07
*/
--create or replace view  v_getcurrent_workflow as
--组信息
with group_info as
(
select gp1.group_intid,gp1.group_id,gp1.group_name,gp1.group_fullname,
       decode(gp1.group_type,3,gp1.group_fullname,2,gp1.group_fullname,gp2.group_fullname) arch,
       decode(gp1.group_type,3,gp1.group_id,2,gp1.group_id,gp2.group_id) archid
from ultraprocess_sysgroup gp1,ultraprocess_sysgroup gp2
where 1=1
and gp1.group_parentid=gp2.group_id
and (gp2.group_type = 3 or gp2.group_type = 2)
),
--组成员
group_user as
(
select WMSYS.WM_CONCAT(us1.user_fullname) person,WMSYS.WM_CONCAT(us1.user_loginname) personid,us2.group_intid from ultraprocess_sysuser us1,ultraprocess_sysgroup us2,ultraprocess_sysgroupuser us3
where 1=1
and us1.user_id=us3.mgroup_userid
and us2.group_id=us3.mgroup_groupid
group by us2.group_intid
),
--映射信息
dp_map as
(
select hj.groupid,hj.processbaseschema from wf_app_dealprocess hj
where  1=1
and  hj.groupid is not null
and  hj.processbaseschema is not null
and  hj.edprocessaction<> 19
group by groupid,processbaseschema
)
/*
--工单信息
,form_info as
(
select info.basesn,info.baseschema,info.basename,info.basestatus,info.baseid,hj.group_x,hj.groupid,hj.flagactive from wf_app_base_infor info,wf_app_dealprocess hj
where 1=1
and info.baseid=hj.processbaseid
and info.baseschema = hj.processbaseschema
and info.basestatus <> '已作废'
and hj.edprocessaction<> 19
)
*/
--展现信息
select dp_map.processbaseschema 工单类别,
group_info.group_intid 组ID,
group_info.group_name 组名,
group_user.person 组成员,
group_user.personid 组成员登录名,
group_info.arch 所属部门,
group_info.archid 所属部门ID
from dp_map,group_info,group_user
where 1=1 
and dp_map.groupid = group_info.group_intid
and group_user.group_intid = group_info.group_intid

转载于:https://www.cnblogs.com/telwanggs/p/4884218.html