基于此复杂语句(语句如下)
with tmp1 as (select orgid from sys_org so start with
so.orgid=17786
connect by prior so.orgsupid = so.orgid ),
tmp4 as (select orgid from sys_org so WHERE ( so.path like '%.17786.%') ),
tmp2 as (select substr(sp.posname, instr(sp.posname, '-') + 1) posname,posgroup from sys_position sp
where sp.posid in (select up.posid from sys_user_pos up where up.isprimary=1 and up.userid = 10000001272605 )),
tmp3 as (select 1 from sys_user_pos up, sys_position p ,tm_sys_job j where up.isprimary=1 and j.jobid = p.jobid and p.posid = up.posid and j.manseq <= '05' and up.userid=10000001272605 ),
tmp5 as (select up.userid from sys_user_pos up,sys_position sp where up.isprimary=1 and up.posid =sp.posid and ( sp.posname like '%-%收派员' or sp.posname like '%-%仓管员' or sp.posname like '%-%运作员' or sp.posname like '%-%司机' ) ),
tmp6 as (select 1
from sys_position sp,table(split(posname,'-')) t2
where sp.posid in (select up.posid
from sys_user_pos up
where up.isprimary = 1
and up.userid = 10000001272605)
and (t2.s_value='综合文员' or t2.s_value='运作文员') ),
temp as (select authoritygroupid, 2 isknowobject from (
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp4 so where t.authorityid = so.orgid and t.typeid =1
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp4 so
where t.orgid = so.orgid and t.typeid =2
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp1 so
where t.orgid = so.orgid and t.typeid =2
union all
select /*+leading(so sur t)*/ t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t
where exists (select 1 from tmp4 so,sys_user_role sur
where t.authorityid = sur.roleid and so.orgid = sur.orgid and t.typeid =3 and sur.isprimary=1 )
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp4 so,sys_user_org suo
where t.authorityid = suo.userid and so.orgid = suo.orgid and t.typeid =4 and suo.isprimary=1
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp4 so,sys_user_org suo
where t.createid = suo.userid and so.orgid = suo.orgid and suo.isprimary=1
union all
select t.authoritygroupid, t.isknowobject from tt_inf_infauthority t,tmp1 where t.typeid = 1 and t.authorityid = tmp1.orgid
union all
select t.authoritygroupid, t.isknowobject from tt_inf_infauthority t,(select distinct posname from tmp2) t2,tmp1 t1,(select distinct posgroup from tmp2) t3
where t.typeid = 2 and t.authorityname = t2.posname and t.posgroup = t3.posgroup and t.orgid = t1.orgid
union all
select t.authoritygroupid, 3 isknowobject from tt_inf_infauthority t,tmp1 where t.typeid = 2 and t.orgid = tmp1.orgid
and ( t.authorityname like '%收派员' or t.authorityname like '%仓管员' or t.authorityname like '%运作员' or t.authorityname like '%司机' )
and exists (select 1 from tmp6)
union all
select t.authoritygroupid, t.isknowobject from tt_inf_infauthority t where t.typeid = 3 and
t.authorityid in (select up.roleid from sys_user_role up where up.isprimary=1 and up.userid = 10000001272605 )
union all
select t.authoritygroupid, t.isknowobject from tt_inf_infauthority t where t.typeid = 4 and t.authorityid = 10000001272605
union all
select t.authoritygroupid, 3 isknowobject from tt_inf_infauthority t,tmp5 up where t.typeid = 4
and t.authorityid = up.userid
and exists (select 1 from tmp6)
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t where t.createid =10000001272605
) group by authoritygroupid)
,
temp1 as
((select INF.INFORMATIONID as INFORMATIONID,
INF.TITLE as TITLE,
to_char(INF.CONTENT) as CONTENT,INF.FILEID as FILEID,
INF.INFTYPEID as INFTYPEID,INF.TAG as TAG,INF.POSID as POSID,
INF.ORGID as ORGID,INF.JOBSID as JOBSID,INF.SECRECYLEVELID as SECRECYLEVELID,
INF.VERSION as VERSION,INF.REMARK as REMARK,INF.RELESETIME as RELESETIME,
INF.FILEIDS as FILEIDS,null as ADTYPE,INF.CREATEBY as CREATEBY,
INF.CREATETIME as CREATETIME,INF.UPDATEBY as UPDATEBY,INF.UPDATETIME as UPDATETIME
,INF.INFSTATE as INFSTATE,null as FSTYPE
,temp.ISKNOWOBJECT as ISKNOWOBJECT,
-2 as funcdepttype
from tt_inf_information inf,temp where inf.authoritygroupid = temp.authoritygroupid
and inf.INFSTATE = 1
and inf.STATE = 1
and inf.LASTVERSION = 1
and ( ((inf.secrecylevelid = 2 and exists (select * from tmp3))
or (inf.secrecylevelid = 2 and temp.isknowobject=2)
)
OR INF.SECRECYLEVELID = 1
OR INF.SECRECYLEVELID = 0
OR INF.CREATEBY =10000001272605
OR INF.UPDATEBY =10000001272605
)
)
union all
(select AD.ADID as INFORMATIONID,
AD.TITLE as TITLE,
null as CONTENT,
null as FILEID,
(select INFTYPE.INFTYPEID from TT_INF_TYPE INFTYPE,tt_inf_adworkflow tia
where tia.adtypeid=ad.adtype and tia.inftypeid=INFTYPE.Inftypeid) as INFTYPEID,
null as TAG,
null as POSID,
To_number(AD.TODUTYORG) as ORGID,
null as JOBSID,
AD.SECRECYLEVELID as SECRECYLEVELID,
1 as VERSION,
null as REMARK,
AD.RELEASEDATE as RELESETIME,
AD.FILEIDS as FILEIDS,
AD.ADTYPE,
to_number(AD.USERNUM) as CREATEBY,
AD.CREATETIME as CREATETIME,
AD.UPDATEBY as UPDATEBY,
AD.UPDATETIME as UPDATETIME
,3 as INFSTATE
,null as FSTYPE
,2 isknowobject,
AD.funcdepttype
from tt_inf_ad ad, sys_org so,temp t where
ad.todutyorg = so.orgid(+)
and ad.authoritygroupid = t.authoritygroupid
and ad.workflow_Status ='2'
)union all
( select FS.FSID as INFORMATIONID,
FS.TITLE as TITLE,
null as CONTENT,
null as FILEID,
(select INFTYPE.INFTYPEID from TT_INF_TYPE INFTYPE,tt_inf_fsworkflow tia
where tia.fstypeid=fs.fs_type and tia.inftypeid=INFTYPE.Inftypeid) as INFTYPEID,
null as TAG,
null as POSID,
To_number(FS.RESPONSIBILITY_DEPARTMENT) as ORGID,
null as JOBSID,
FS.SECRECYLEVELID as SECRECYLEVELID,
FS.VERSION_NUM as VERSION,
null as REMARK,
FS.RELEASE_DATE as RELESETIME,
(fs.fsfiles ||decode(fs.fsfiles,null,'',',')||fs.accessories) as FILEIDS,
null as ADTYPE,
(SELECT userId FROM SYS_USER T WHERE T.ACCOUNT=USERNAME) AS CREATEBY,
FS.DATE_OF_APPLICATION AS CREATETIME,
(SELECT userId FROM SYS_USER T WHERE T.ACCOUNT=USERNAME) AS UPDATEBY,
FS.EFFECTIVE_TIME as UPDATETIME
,3 as INFSTATE
,FS.FS_TYPE as FSTYPE
,2 as isknowobject,
FS.funcdepttype
from tt_inf_fs fs, sys_org so,temp t where
fs.responsibility_department = so.orgid(+)
and fs.authoritygroupid = t.authoritygroupid
and fs.workflow_Status ='2'
)
),
temp2 as
(select temp1.INFORMATIONID,temp1.TITLE,temp1.CONTENT,
temp1.FILEID,temp1.INFTYPEID,temp1.TAG,
temp1.POSID,temp1.ORGID,temp1.JOBSID,
temp1.SECRECYLEVELID,temp1.VERSION,temp1.REMARK,
temp1.RELESETIME,temp1.FILEIDS,temp1.ADTYPE,
temp1.CREATEBY,temp1.CREATETIME,temp1.UPDATEBY,
temp1.UPDATETIME,temp1.INFSTATE,
max(temp1.isknowobject) isknowobject,
temp1.FSTYPE,org.orgname,pos.posname,
JOB.POSGROUP jobname,INFTYPE.Inftypename,INFTYPE.define,
count(decode( INFTYPE.define,'2',0,null))over() fsCount,
count(decode( INFTYPE.define,'1',null,'2',null,0))over() innerCount,
funcdepttype
from temp1,
TT_INF_TYPE INFTYPE,
SYS_ORG ORG,
SYS_POSITION POS,
SYS_POSITION JOB
where temp1.POSID = POS.POSID(+)
and temp1.orgid = org.orgid(+)
and temp1.INFTYPEID = INFTYPE.INFTYPEID(+)
and temp1.JOBSID = JOB.POSID(+)
group by temp1.INFORMATIONID,temp1.TITLE,temp1.CONTENT,
temp1.FILEID,temp1.INFTYPEID,temp1.TAG,
temp1.POSID,temp1.ORGID,temp1.JOBSID,
temp1.SECRECYLEVELID,temp1.VERSION,
temp1.REMARK,temp1.RELESETIME,temp1.FILEIDS,
temp1.ADTYPE,temp1.CREATEBY,temp1.CREATETIME,
temp1.UPDATEBY,temp1.UPDATETIME,temp1.INFSTATE,
temp1.FSTYPE,org.orgname,pos.posname,
temp1.funcdepttype,
JOB.POSGROUP,INFTYPE.Inftypename,INFTYPE.define)
select * from
(
select temp2.*,
(CASE WHEN re.remindid >0 THEN 'YES' ELSE 'NO' END) isRead
from temp2
left join ( select * from TT_ASS_REMIND_READ where user_id = '10000001272605' and HAS_READ = 3 )re on re.remindid = temp2.INFORMATIONID
where temp2.define = '1'
union all
select
NULL AS INFORMATIONID,NULL as TITLE,NULL AS CONTENT,
NULL as FILEID,NULL as INFTYPEID,NULL as TAG,
NULL as POSID,NULL as ORGID, NULL as JOBSID,
NULL as SECRECYLEVELID,NULL as VERSION,NULL as REMARK,
TO_DATE('1770-11-11 00:00:00','yyyy-mm-dd hh24:mi:ss') as RELESETIME,
NULL as FILEIDS,NULL as ADTYPE,NULL as CREATEBY,
NULL as CREATETIME,NULL as UPDATEBY,NULL as UPDATETIME,
NULL as INFSTATE,null as FSTYPE,NULL as ISKNOWOBJECT,
NULL orgname,NULL posname,NULL jobname,
NULL Inftypename,NULL define,
decode((select sum (fsCount) from temp2 where rownum=1),null,0,(select sum (fscount) from temp2 where rownum=1)) fscount,
decode((select sum (innerCount) from temp2 where rownum=1),null,0,(select sum (innerCount)-1 from temp2 where rownum=1)) innerCount,
-2 funcdepttype,
NULL isRead
from dual )
ORDER BY RELESETIME DESC;
中性能是由语句中的like '%%'引起,并发现字段的特殊性,被查找的数据为path中的“。”分隔内容,故可对sys_org表及字段path用table函数自关联建成物化视图,并且根据实际情况每两个小时刷新一次,具体如下:
create materialized view sysorg_paths
refresh complete
start with sysdate
next sysdate + 1/12
as
(
select t2.*,t1.* from sys_org t1 ,table(split(path,'.')) t2
) ;
create index idx_sysorg_paths on sysorg_paths(s_value);
语句改写如下:
with tmp1 as (select orgid from sys_org so start with
so.orgid=17786
connect by prior so.orgsupid = so.orgid ),
tmp4 as (select orgid from sysorg_paths so WHERE ( so.s_value ='17786' ) ),
tmp2 as (select substr(sp.posname, instr(sp.posname, '-') + 1) posname,posgroup from sys_position sp
where sp.posid in (select up.posid from sys_user_pos up where up.isprimary=1 and up.userid = 10000001272605 )),
tmp3 as (select 1 from sys_user_pos up, sys_position p ,tm_sys_job j where up.isprimary=1 and j.jobid = p.jobid and p.posid = up.posid and j.manseq <= '05' and up.userid=10000001272605 ),
tmp5 as (select up.userid from sys_user_pos up,sys_position sp where up.isprimary=1 and up.posid =sp.posid and ( sp.posname like '%-%收派员' or sp.posname like '%-%仓管员' or sp.posname like '%-%运作员' or sp.posname like '%-%司机' ) ),
tmp6 as (select 1
from sys_position sp,table(split(posname,'-')) t2
where sp.posid in (select up.posid
from sys_user_pos up
where up.isprimary = 1
and up.userid = 10000001272605)
and (t2.s_value='综合文员' or t2.s_value='运作文员') ),
temp as (select authoritygroupid, 2 isknowobject from (
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp4 so where t.authorityid = so.orgid and t.typeid =1
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp4 so
where t.orgid = so.orgid and t.typeid =2
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp1 so
where t.orgid = so.orgid and t.typeid =2
union all
select /*+leading(so sur t)*/ t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t
where exists (select 1 from tmp4 so,sys_user_role sur
where t.authorityid = sur.roleid and so.orgid = sur.orgid and t.typeid =3 and sur.isprimary=1 )
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp4 so,sys_user_org suo
where t.authorityid = suo.userid and so.orgid = suo.orgid and t.typeid =4 and suo.isprimary=1
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp4 so,sys_user_org suo
where t.createid = suo.userid and so.orgid = suo.orgid and suo.isprimary=1
union all
select t.authoritygroupid, t.isknowobject from tt_inf_infauthority t,tmp1 where t.typeid = 1 and t.authorityid = tmp1.orgid
union all
select t.authoritygroupid, t.isknowobject from tt_inf_infauthority t,(select distinct posname from tmp2) t2,tmp1 t1,(select distinct posgroup from tmp2) t3
where t.typeid = 2 and t.authorityname = t2.posname and t.posgroup = t3.posgroup and t.orgid = t1.orgid
union all
select t.authoritygroupid, 3 isknowobject from tt_inf_infauthority t,tmp1 where t.typeid = 2 and t.orgid = tmp1.orgid
and ( t.authorityname like '%收派员' or t.authorityname like '%仓管员' or t.authorityname like '%运作员' or t.authorityname like '%司机' )
and exists (select 1 from tmp6)
union all
select t.authoritygroupid, t.isknowobject from tt_inf_infauthority t where t.typeid = 3 and
t.authorityid in (select up.roleid from sys_user_role up where up.isprimary=1 and up.userid = 10000001272605 )
union all
select t.authoritygroupid, t.isknowobject from tt_inf_infauthority t where t.typeid = 4 and t.authorityid = 10000001272605
union all
select t.authoritygroupid, 3 isknowobject from tt_inf_infauthority t,tmp5 up where t.typeid = 4
and t.authorityid = up.userid
and exists (select 1 from tmp6)
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t where t.createid =10000001272605
) group by authoritygroupid)
,
temp1 as
((select INF.INFORMATIONID as INFORMATIONID,
INF.TITLE as TITLE,
to_char(INF.CONTENT) as CONTENT,INF.FILEID as FILEID,
INF.INFTYPEID as INFTYPEID,INF.TAG as TAG,INF.POSID as POSID,
INF.ORGID as ORGID,INF.JOBSID as JOBSID,INF.SECRECYLEVELID as SECRECYLEVELID,
INF.VERSION as VERSION,INF.REMARK as REMARK,INF.RELESETIME as RELESETIME,
INF.FILEIDS as FILEIDS,null as ADTYPE,INF.CREATEBY as CREATEBY,
INF.CREATETIME as CREATETIME,INF.UPDATEBY as UPDATEBY,INF.UPDATETIME as UPDATETIME
,INF.INFSTATE as INFSTATE,null as FSTYPE
,temp.ISKNOWOBJECT as ISKNOWOBJECT,
-2 as funcdepttype
from tt_inf_information inf,temp where inf.authoritygroupid = temp.authoritygroupid
and inf.INFSTATE = 1
and inf.STATE = 1
and inf.LASTVERSION = 1
and ( ((inf.secrecylevelid = 2 and exists (select * from tmp3))
or (inf.secrecylevelid = 2 and temp.isknowobject=2)
)
OR INF.SECRECYLEVELID = 1
OR INF.SECRECYLEVELID = 0
OR INF.CREATEBY =10000001272605
OR INF.UPDATEBY =10000001272605
)
)
union all
(select AD.ADID as INFORMATIONID,
AD.TITLE as TITLE,
null as CONTENT,
null as FILEID,
(select INFTYPE.INFTYPEID from TT_INF_TYPE INFTYPE,tt_inf_adworkflow tia
where tia.adtypeid=ad.adtype and tia.inftypeid=INFTYPE.Inftypeid) as INFTYPEID,
null as TAG,
null as POSID,
To_number(AD.TODUTYORG) as ORGID,
null as JOBSID,
AD.SECRECYLEVELID as SECRECYLEVELID,
1 as VERSION,
null as REMARK,
AD.RELEASEDATE as RELESETIME,
AD.FILEIDS as FILEIDS,
AD.ADTYPE,
to_number(AD.USERNUM) as CREATEBY,
AD.CREATETIME as CREATETIME,
AD.UPDATEBY as UPDATEBY,
AD.UPDATETIME as UPDATETIME
,3 as INFSTATE
,null as FSTYPE
,2 isknowobject,
AD.funcdepttype
from tt_inf_ad ad, sys_org so,temp t where
ad.todutyorg = so.orgid(+)
and ad.authoritygroupid = t.authoritygroupid
and ad.workflow_Status ='2'
)union all
( select FS.FSID as INFORMATIONID,
FS.TITLE as TITLE,
null as CONTENT,
null as FILEID,
(select INFTYPE.INFTYPEID from TT_INF_TYPE INFTYPE,tt_inf_fsworkflow tia
where tia.fstypeid=fs.fs_type and tia.inftypeid=INFTYPE.Inftypeid) as INFTYPEID,
null as TAG,
null as POSID,
To_number(FS.RESPONSIBILITY_DEPARTMENT) as ORGID,
null as JOBSID,
FS.SECRECYLEVELID as SECRECYLEVELID,
FS.VERSION_NUM as VERSION,
null as REMARK,
FS.RELEASE_DATE as RELESETIME,
(fs.fsfiles ||decode(fs.fsfiles,null,'',',')||fs.accessories) as FILEIDS,
null as ADTYPE,
(SELECT userId FROM SYS_USER T WHERE T.ACCOUNT=USERNAME) AS CREATEBY,
FS.DATE_OF_APPLICATION AS CREATETIME,
(SELECT userId FROM SYS_USER T WHERE T.ACCOUNT=USERNAME) AS UPDATEBY,
FS.EFFECTIVE_TIME as UPDATETIME
,3 as INFSTATE
,FS.FS_TYPE as FSTYPE
,2 as isknowobject,
FS.funcdepttype
from tt_inf_fs fs, sys_org so,temp t where
fs.responsibility_department = so.orgid(+)
and fs.authoritygroupid = t.authoritygroupid
and fs.workflow_Status ='2'
)
),
temp2 as
(select temp1.INFORMATIONID,temp1.TITLE,temp1.CONTENT,
temp1.FILEID,temp1.INFTYPEID,temp1.TAG,
temp1.POSID,temp1.ORGID,temp1.JOBSID,
temp1.SECRECYLEVELID,temp1.VERSION,temp1.REMARK,
temp1.RELESETIME,temp1.FILEIDS,temp1.ADTYPE,
temp1.CREATEBY,temp1.CREATETIME,temp1.UPDATEBY,
temp1.UPDATETIME,temp1.INFSTATE,
max(temp1.isknowobject) isknowobject,
temp1.FSTYPE,org.orgname,pos.posname,
JOB.POSGROUP jobname,INFTYPE.Inftypename,INFTYPE.define,
count(decode( INFTYPE.define,'2',0,null))over() fsCount,
count(decode( INFTYPE.define,'1',null,'2',null,0))over() innerCount,
funcdepttype
from temp1,
TT_INF_TYPE INFTYPE,
SYS_ORG ORG,
SYS_POSITION POS,
SYS_POSITION JOB
where temp1.POSID = POS.POSID(+)
and temp1.orgid = org.orgid(+)
and temp1.INFTYPEID = INFTYPE.INFTYPEID(+)
and temp1.JOBSID = JOB.POSID(+)
group by temp1.INFORMATIONID,temp1.TITLE,temp1.CONTENT,
temp1.FILEID,temp1.INFTYPEID,temp1.TAG,
temp1.POSID,temp1.ORGID,temp1.JOBSID,
temp1.SECRECYLEVELID,temp1.VERSION,
temp1.REMARK,temp1.RELESETIME,temp1.FILEIDS,
temp1.ADTYPE,temp1.CREATEBY,temp1.CREATETIME,
temp1.UPDATEBY,temp1.UPDATETIME,temp1.INFSTATE,
temp1.FSTYPE,org.orgname,pos.posname,
temp1.funcdepttype,
JOB.POSGROUP,INFTYPE.Inftypename,INFTYPE.define)
select * from
(
select temp2.*,
(CASE WHEN re.remindid >0 THEN 'YES' ELSE 'NO' END) isRead
from temp2
left join ( select * from TT_ASS_REMIND_READ where user_id = '10000001272605' and HAS_READ = 3 )re on re.remindid = temp2.INFORMATIONID
where temp2.define = '1'
union all
select
NULL AS INFORMATIONID,NULL as TITLE,NULL AS CONTENT,
NULL as FILEID,NULL as INFTYPEID,NULL as TAG,
NULL as POSID,NULL as ORGID, NULL as JOBSID,
NULL as SECRECYLEVELID,NULL as VERSION,NULL as REMARK,
TO_DATE('1770-11-11 00:00:00','yyyy-mm-dd hh24:mi:ss') as RELESETIME,
NULL as FILEIDS,NULL as ADTYPE,NULL as CREATEBY,
NULL as CREATETIME,NULL as UPDATEBY,NULL as UPDATETIME,
NULL as INFSTATE,null as FSTYPE,NULL as ISKNOWOBJECT,
NULL orgname,NULL posname,NULL jobname,
NULL Inftypename,NULL define,
decode((select sum (fsCount) from temp2 where rownum=1),null,0,(select sum (fscount) from temp2 where rownum=1)) fscount,
decode((select sum (innerCount) from temp2 where rownum=1),null,0,(select sum (innerCount)-1 from temp2 where rownum=1)) innerCount,
-2 funcdepttype,
NULL isRead
from dual )
ORDER BY RELESETIME DESC;
因生产数据库掌握在我们维护人员手中,为了更好的在线优化,建立带数视图,帮建立了如下包:
create or replace package sysorgpath_view_param
is
function set_param(num number) return number;
function get_param
return number;
end sysorgpath_view_param;
create or replace package body sysorgpath_view_param is
paramValue number;
function set_param(num number) return number is
begin
paramValue:=num;
return num;
end;
function get_param return number is
begin
return paramValue;
end;
end sysorgpath_view_param;
并创建视图如下:
create or replace view sysorg_path_views as
select orgid,orgname from sysorg_paths where s_value=to_char(sysorgpath_view_param.get_param());
故上述语句可改写如下:
with tmp1 as (select orgid from sys_org so start with
so.orgid=17786
connect by prior so.orgsupid = so.orgid ),
tmp4 as (select orgid from sysorg_path_views so WHERE ( sysorgpath_view_param.set_param(17786)=17786;) ),
tmp2 as (select substr(sp.posname, instr(sp.posname, '-') + 1) posname,posgroup from sys_position sp
where sp.posid in (select up.posid from sys_user_pos up where up.isprimary=1 and up.userid = 10000001272605 )),
tmp3 as (select 1 from sys_user_pos up, sys_position p ,tm_sys_job j where up.isprimary=1 and j.jobid = p.jobid and p.posid = up.posid and j.manseq <= '05' and up.userid=10000001272605 ),
tmp5 as (select up.userid from sys_user_pos up,sys_position sp where up.isprimary=1 and up.posid =sp.posid and ( sp.posname like '%-%收派员' or sp.posname like '%-%仓管员' or sp.posname like '%-%运作员' or sp.posname like '%-%司机' ) ),
tmp6 as (select 1
from sys_position sp,table(split(posname,'-')) t2
where sp.posid in (select up.posid
from sys_user_pos up
where up.isprimary = 1
and up.userid = 10000001272605)
and (t2.s_value='综合文员' or t2.s_value='运作文员') ),
temp as (select authoritygroupid, 2 isknowobject from (
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp4 so where t.authorityid = so.orgid and t.typeid =1
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp4 so
where t.orgid = so.orgid and t.typeid =2
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp1 so
where t.orgid = so.orgid and t.typeid =2
union all
select /*+leading(so sur t)*/ t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t
where exists (select 1 from tmp4 so,sys_user_role sur
where t.authorityid = sur.roleid and so.orgid = sur.orgid and t.typeid =3 and sur.isprimary=1 )
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp4 so,sys_user_org suo
where t.authorityid = suo.userid and so.orgid = suo.orgid and t.typeid =4 and suo.isprimary=1
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t,tmp4 so,sys_user_org suo
where t.createid = suo.userid and so.orgid = suo.orgid and suo.isprimary=1
union all
select t.authoritygroupid, t.isknowobject from tt_inf_infauthority t,tmp1 where t.typeid = 1 and t.authorityid = tmp1.orgid
union all
select t.authoritygroupid, t.isknowobject from tt_inf_infauthority t,(select distinct posname from tmp2) t2,tmp1 t1,(select distinct posgroup from tmp2) t3
where t.typeid = 2 and t.authorityname = t2.posname and t.posgroup = t3.posgroup and t.orgid = t1.orgid
union all
select t.authoritygroupid, 3 isknowobject from tt_inf_infauthority t,tmp1 where t.typeid = 2 and t.orgid = tmp1.orgid
and ( t.authorityname like '%收派员' or t.authorityname like '%仓管员' or t.authorityname like '%运作员' or t.authorityname like '%司机' )
and exists (select 1 from tmp6)
union all
select t.authoritygroupid, t.isknowobject from tt_inf_infauthority t where t.typeid = 3 and
t.authorityid in (select up.roleid from sys_user_role up where up.isprimary=1 and up.userid = 10000001272605 )
union all
select t.authoritygroupid, t.isknowobject from tt_inf_infauthority t where t.typeid = 4 and t.authorityid = 10000001272605
union all
select t.authoritygroupid, 3 isknowobject from tt_inf_infauthority t,tmp5 up where t.typeid = 4
and t.authorityid = up.userid
and exists (select 1 from tmp6)
union all
select t.authoritygroupid, 2 isknowobject from tt_inf_infauthority t where t.createid =10000001272605
) group by authoritygroupid)
,
temp1 as
((select INF.INFORMATIONID as INFORMATIONID,
INF.TITLE as TITLE,
to_char(INF.CONTENT) as CONTENT,INF.FILEID as FILEID,
INF.INFTYPEID as INFTYPEID,INF.TAG as TAG,INF.POSID as POSID,
INF.ORGID as ORGID,INF.JOBSID as JOBSID,INF.SECRECYLEVELID as SECRECYLEVELID,
INF.VERSION as VERSION,INF.REMARK as REMARK,INF.RELESETIME as RELESETIME,
INF.FILEIDS as FILEIDS,null as ADTYPE,INF.CREATEBY as CREATEBY,
INF.CREATETIME as CREATETIME,INF.UPDATEBY as UPDATEBY,INF.UPDATETIME as UPDATETIME
,INF.INFSTATE as INFSTATE,null as FSTYPE
,temp.ISKNOWOBJECT as ISKNOWOBJECT,
-2 as funcdepttype
from tt_inf_information inf,temp where inf.authoritygroupid = temp.authoritygroupid
and inf.INFSTATE = 1
and inf.STATE = 1
and inf.LASTVERSION = 1
and ( ((inf.secrecylevelid = 2 and exists (select * from tmp3))
or (inf.secrecylevelid = 2 and temp.isknowobject=2)
)
OR INF.SECRECYLEVELID = 1
OR INF.SECRECYLEVELID = 0
OR INF.CREATEBY =10000001272605
OR INF.UPDATEBY =10000001272605
)
)
union all
(select AD.ADID as INFORMATIONID,
AD.TITLE as TITLE,
null as CONTENT,
null as FILEID,
(select INFTYPE.INFTYPEID from TT_INF_TYPE INFTYPE,tt_inf_adworkflow tia
where tia.adtypeid=ad.adtype and tia.inftypeid=INFTYPE.Inftypeid) as INFTYPEID,
null as TAG,
null as POSID,
To_number(AD.TODUTYORG) as ORGID,
null as JOBSID,
AD.SECRECYLEVELID as SECRECYLEVELID,
1 as VERSION,
null as REMARK,
AD.RELEASEDATE as RELESETIME,
AD.FILEIDS as FILEIDS,
AD.ADTYPE,
to_number(AD.USERNUM) as CREATEBY,
AD.CREATETIME as CREATETIME,
AD.UPDATEBY as UPDATEBY,
AD.UPDATETIME as UPDATETIME
,3 as INFSTATE
,null as FSTYPE
,2 isknowobject,
AD.funcdepttype
from tt_inf_ad ad, sys_org so,temp t where
ad.todutyorg = so.orgid(+)
and ad.authoritygroupid = t.authoritygroupid
and ad.workflow_Status ='2'
)union all
( select FS.FSID as INFORMATIONID,
FS.TITLE as TITLE,
null as CONTENT,
null as FILEID,
(select INFTYPE.INFTYPEID from TT_INF_TYPE INFTYPE,tt_inf_fsworkflow tia
where tia.fstypeid=fs.fs_type and tia.inftypeid=INFTYPE.Inftypeid) as INFTYPEID,
null as TAG,
null as POSID,
To_number(FS.RESPONSIBILITY_DEPARTMENT) as ORGID,
null as JOBSID,
FS.SECRECYLEVELID as SECRECYLEVELID,
FS.VERSION_NUM as VERSION,
null as REMARK,
FS.RELEASE_DATE as RELESETIME,
(fs.fsfiles ||decode(fs.fsfiles,null,'',',')||fs.accessories) as FILEIDS,
null as ADTYPE,
(SELECT userId FROM SYS_USER T WHERE T.ACCOUNT=USERNAME) AS CREATEBY,
FS.DATE_OF_APPLICATION AS CREATETIME,
(SELECT userId FROM SYS_USER T WHERE T.ACCOUNT=USERNAME) AS UPDATEBY,
FS.EFFECTIVE_TIME as UPDATETIME
,3 as INFSTATE
,FS.FS_TYPE as FSTYPE
,2 as isknowobject,
FS.funcdepttype
from tt_inf_fs fs, sys_org so,temp t where
fs.responsibility_department = so.orgid(+)
and fs.authoritygroupid = t.authoritygroupid
and fs.workflow_Status ='2'
)
),
temp2 as
(select temp1.INFORMATIONID,temp1.TITLE,temp1.CONTENT,
temp1.FILEID,temp1.INFTYPEID,temp1.TAG,
temp1.POSID,temp1.ORGID,temp1.JOBSID,
temp1.SECRECYLEVELID,temp1.VERSION,temp1.REMARK,
temp1.RELESETIME,temp1.FILEIDS,temp1.ADTYPE,
temp1.CREATEBY,temp1.CREATETIME,temp1.UPDATEBY,
temp1.UPDATETIME,temp1.INFSTATE,
max(temp1.isknowobject) isknowobject,
temp1.FSTYPE,org.orgname,pos.posname,
JOB.POSGROUP jobname,INFTYPE.Inftypename,INFTYPE.define,
count(decode( INFTYPE.define,'2',0,null))over() fsCount,
count(decode( INFTYPE.define,'1',null,'2',null,0))over() innerCount,
funcdepttype
from temp1,
TT_INF_TYPE INFTYPE,
SYS_ORG ORG,
SYS_POSITION POS,
SYS_POSITION JOB
where temp1.POSID = POS.POSID(+)
and temp1.orgid = org.orgid(+)
and temp1.INFTYPEID = INFTYPE.INFTYPEID(+)
and temp1.JOBSID = JOB.POSID(+)
group by temp1.INFORMATIONID,temp1.TITLE,temp1.CONTENT,
temp1.FILEID,temp1.INFTYPEID,temp1.TAG,
temp1.POSID,temp1.ORGID,temp1.JOBSID,
temp1.SECRECYLEVELID,temp1.VERSION,
temp1.REMARK,temp1.RELESETIME,temp1.FILEIDS,
temp1.ADTYPE,temp1.CREATEBY,temp1.CREATETIME,
temp1.UPDATEBY,temp1.UPDATETIME,temp1.INFSTATE,
temp1.FSTYPE,org.orgname,pos.posname,
temp1.funcdepttype,
JOB.POSGROUP,INFTYPE.Inftypename,INFTYPE.define)
select * from
(
select temp2.*,
(CASE WHEN re.remindid >0 THEN 'YES' ELSE 'NO' END) isRead
from temp2
left join ( select * from TT_ASS_REMIND_READ where user_id = '10000001272605' and HAS_READ = 3 )re on re.remindid = temp2.INFORMATIONID
where temp2.define = '1'
union all
select
NULL AS INFORMATIONID,NULL as TITLE,NULL AS CONTENT,
NULL as FILEID,NULL as INFTYPEID,NULL as TAG,
NULL as POSID,NULL as ORGID, NULL as JOBSID,
NULL as SECRECYLEVELID,NULL as VERSION,NULL as REMARK,
TO_DATE('1770-11-11 00:00:00','yyyy-mm-dd hh24:mi:ss') as RELESETIME,
NULL as FILEIDS,NULL as ADTYPE,NULL as CREATEBY,
NULL as CREATETIME,NULL as UPDATEBY,NULL as UPDATETIME,
NULL as INFSTATE,null as FSTYPE,NULL as ISKNOWOBJECT,
NULL orgname,NULL posname,NULL jobname,
NULL Inftypename,NULL define,
decode((select sum (fsCount) from temp2 where rownum=1),null,0,(select sum (fscount) from temp2 where rownum=1)) fscount,
decode((select sum (innerCount) from temp2 where rownum=1),null,0,(select sum (innerCount)-1 from temp2 where rownum=1)) innerCount,
-2 funcdepttype,
NULL isRead
from dual )
ORDER BY RELESETIME DESC;
最后把上述语句到生产环境上一试,发现所用时间约为原来的十分之一。其实还可继续用视图简化该语句,用带参视图,以便能够在线调整,以便加相应的查询指示