Chinese

单位里的一个复杂sql。
不太清楚autotrace的分析。
Statistics
-------------------------------------------------------
7812 recursive calls
1 db block gets
1780003 consistent gets
0 physical reads
184 redo size
563 bytes sent via SQL*Net to client
7575 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
23447 sorts (memory)
0 sorts (disk)
1 rows processed



能否给一下建议啊？







insert into temp_query_auth
select b.guid
from (select /*+rule */
b.guid,
min(case when b.checkoutuser = '32500EC798FB40B2BA88AA227829DF08'
then '0'
when (b.committime is null or b.committime > sysdate) and b.locationuser = '32500EC798FB40B2BA88AA227829DF08'
then '0'
when b.committime <= sysdate
then decode(b.owneruser, '32500EC798FB40B2BA88AA227829DF08', e.owneroperselect,e.notowneroperselect)
end)
keep(dense_rank first order by e.aclrowindex) operselect
from ma_foundation b inner join (select distinct
f$.guid
from ma_foundation f$,
ma_ec_class ecclass
,(select b.foundationguid foundationguid
from ma_foundation_folder b,
(select m.guid
from (select n.guid,
ltrim(substr(sys_connect_by_path(substr(pkg_sa_util.fun_get_folderauth_all('32500EC798FB40B2BA88AA227829DF08',
'EBDD840BEE8344FE96B113E052A164B4',
'9F6E7E1CA8384F62B245AD697EC77254',
n.guid),1, 1),','),3),',01') folderacl
from ma_folder n
start with n.guid = '40786C4C5A844BA9B0D99E85D29CFC82'
connect by prior n.guid = n.parentguid
) m
where m.folderacl is null
) c
where b.folderguid = c.guid
) folder$
where f$.guid = ecclass.classguid(+)
and ((ecclass.classguid is not null and f$.sourceguid is not null)
or (ecclass.classguid is null and f$.checkoutuser = '32500EC798FB40B2BA88AA227829DF08' and f$.sourceguid is not null)
or (ecclass.classguid is null and f$.checkoutuser != '32500EC798FB40B2BA88AA227829DF08' and f$.sourceguid is null)
or f$.checkoutuser is null)
and (f$.committime <= sysdate)
and f$.fotype = '1'
and exists (select 1
from sa_bm_bo b,
sa_businessobject c,
ma_class d,
sa_group_bm e
where b.boguid = c.guid
and c.classguid = d.guid
and b.bmguid = e.bmguid
and d.nonqueryable = 'N'
and e.groupguid = 'EBDD840BEE8344FE96B113E052A164B4'
and c.guid = f$.boguid
)
and ((upper(F$.ID) like upper('%Id5998800100018%') OR upper(F$.NAME) like upper('%Id5998800100018%') OR upper(F$.ALTERID) like upper('%Id5998800100018%')) AND (F$.obsoletetime is null or F$.obsoletetime > sysdate ))
and f$.guid = folder$.foundationguid
) c
on c.guid = b.guid
left join temp_query_acl e
on (e.m_status is null or e.m_status = case when b.obsoletetime <= sysdate
then 'OBS'
when b.effectivetime is null or b.effectivetime > sysdate
then 'CRT'
when b.status is null
then 'WIP'
else b.status
end)
and (e.m_owninggroup is null or e.m_owninggroup = b.ownergroup)
and (e.m_classification is null or e.m_classification = b.classification)
and (e.m_class is null or e.m_class = b.classguid)
and (e.m_lifecyclephase is null or e.m_lifecyclephase = b.lifecyclephase)
and (e.m_revision is null or b.latestrevision like '%' || e.m_revision || '%')
and e.libraryguid = b.locationlib
where decode(b.owneruser, '32500EC798FB40B2BA88AA227829DF08', e.owneroperselect,
e.notowneroperselect) is not null
or ((b.committime is null or b.committime > sysdate) and b.locationuser = '32500EC798FB40B2BA88AA227829DF08')
or b.checkoutuser = '32500EC798FB40B2BA88AA227829DF08'
group by b.guid
) b
where b.operselect in ('0', '1')