我知道它可以优化,至少in关键字就可以。但是我觉得还应该有更好的。。
谁来试试看?
SELECT MODID,MODNAME,PARENTMODID,RELATIVEURL,DISPLAYORDER,MODLEVEL,max(CANEDIT) as CANEDIT,MODSORT
FROM (
SELECT AA.*,BB.CANEDIT
FROM(
SELECT a.MODID,a.MODNAME,a.PARENTMODID,a.RELATIVEURL,a.DISPLAYORDER,b.MODLEVEL,b.modsort
FROM AP_MODS a, TEMP_MOD b
WHERE a.MODID = b.MODID(+)
) AA,AP_MODS_ROLES BB
WHERE AA.MODID = BB.MODID(+)
AND BB.ROLEID in (select ROLEID from AP_USERS_APPS where USERID='1111') -- role
)
GROUP BY MODID,MODNAME,PARENTMODID,RELATIVEURL,DISPLAYORDER,MODLEVEL,MODSORT
ORDER BY MODLEVEL,MODSORT;
谁来试试看?
SELECT MODID,MODNAME,PARENTMODID,RELATIVEURL,DISPLAYORDER,MODLEVEL,max(CANEDIT) as CANEDIT,MODSORT
FROM (
SELECT AA.*,BB.CANEDIT
FROM(
SELECT a.MODID,a.MODNAME,a.PARENTMODID,a.RELATIVEURL,a.DISPLAYORDER,b.MODLEVEL,b.modsort
FROM AP_MODS a, TEMP_MOD b
WHERE a.MODID = b.MODID(+)
) AA,AP_MODS_ROLES BB
WHERE AA.MODID = BB.MODID(+)
AND BB.ROLEID in (select ROLEID from AP_USERS_APPS where USERID='1111') -- role
)
GROUP BY MODID,MODNAME,PARENTMODID,RELATIVEURL,DISPLAYORDER,MODLEVEL,MODSORT
ORDER BY MODLEVEL,MODSORT;
BB.ROLEID in (select ROLEID from AP_USERS_APPS where USERID='1111') 改为
exists (select 1 from AP_USERS_APPS ap where bb.roleid=ap.roleid and USERID='1111')
至于order by,group by,左连接 不用不行啊
因为最内的select是检索临时表,不知道能不能建view 或者其他什么东西优化。。
我的版本是9i
不知道oracle中view可不可以做到这点
然后找一个等价的做法
比如调整select的顺序
让可以减少记录数的select 先执行
max(c.CANEDIT) as CANEDIT
FROM AP_MODS a
LEFT JOIN TEMP_MOD b ON a.MODID = b.MODID
LEFT JOIN (SELECT CANEDIT,MODID FROM AP_MODS_ROLES m
WHERE EXISTS (select 1 FROM AP_USERS_APPS
WHERE ROLEID=m.ROLEID AND USERID='1111'
)
) c ON a.MODID=c.MODID
GROUP BY a.MODID,a.MODNAME,a.PARENTMODID,a.RELATIVEURL,a.DISPLAYORDER,b.MODLEVEL,b.modsort
ORDER BY b.MODLEVEL,b.MODSORT;
最里面的selece就是最少的检索而且是最基本的。
看来也只能如此了。
揭帖