select a.inst_id , count(*) from
(
SELECT L.USER_ID, R.RESP_APPL_ID, R.RESPONSIBILITY_ID, F.AUDSID,S.SID,
USR.USER_NAME, RSP.RESPONSIBILITY_NAME, FRM.USER_FORM_NAME, S.INST_ID
FROM FND_RESPONSIBILITY_TL RSP, -- 3188 rows
FND_FORM_TL FRM, -- 8992
FND_USER USR, -- 39400
FND_LOGINS L, -- 510000
FND_LOGIN_RESPONSIBILITIES R, --- 280000
FND_LOGIN_RESP_FORMS F, --- 1120000
GV$SESSION S --- VIEW 300
WHERE R.LOGIN_ID = F.LOGIN_ID
AND R.LOGIN_RESP_ID = F.LOGIN_RESP_ID -- R, F 表中 LOGIN_ID , LOGIN_RESP_ID 联合索引
AND L.LOGIN_ID = R.LOGIN_ID --- L 表中 LOGIN_ID 唯一索引
AND L.END_TIME IS NULL ----- L 表中 END_TIME is NULL 为 38万
AND R.END_TIME IS NULL ----- R 表中 END_TIME is NULL 为 10 万
AND F.END_TIME IS NULL ----- F 表中 END_TIME is NULL 为 12 万
AND L.USER_ID = USR.USER_ID -- L, USR 表中 USER_ID 存在索引 , USR中是唯一的 。
AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID -- R 中RESPONSIBILITY_ID 无索引(只有56个不同值),RESP_APPL_ID无索引
AND R.RESP_APPL_ID = RSP.APPLICATION_ID ---(只有16个不同值) , RSP 中 APPLICATION_ID与RESPONSIBILITY_ID 联合索引
AND RSP.LANGUAGE = USERENV ('LANG')
AND F.FORM_APPL_ID = FRM.APPLICATION_ID --- F 表FORM_APPL_ID 与FORM_ID 联合索引
AND F.FORM_ID = FRM.FORM_ID --- FRM 表中 FORM_APPL_ID 与FORM_ID 联合唯一索引
AND FRM.LANGUAGE = USERENV ('LANG')
AND F.AUDSID = S.AUDSID --- F 表中AUDSID 有索引
) a
group by a.inst_id ;
其中中间的select 是一个view, 这里把它展开 。
这种SQL 优化如何下手 ? Index scan , full table scan , 表连接 , 还是Cost ??
(
SELECT L.USER_ID, R.RESP_APPL_ID, R.RESPONSIBILITY_ID, F.AUDSID,S.SID,
USR.USER_NAME, RSP.RESPONSIBILITY_NAME, FRM.USER_FORM_NAME, S.INST_ID
FROM FND_RESPONSIBILITY_TL RSP, -- 3188 rows
FND_FORM_TL FRM, -- 8992
FND_USER USR, -- 39400
FND_LOGINS L, -- 510000
FND_LOGIN_RESPONSIBILITIES R, --- 280000
FND_LOGIN_RESP_FORMS F, --- 1120000
GV$SESSION S --- VIEW 300
WHERE R.LOGIN_ID = F.LOGIN_ID
AND R.LOGIN_RESP_ID = F.LOGIN_RESP_ID -- R, F 表中 LOGIN_ID , LOGIN_RESP_ID 联合索引
AND L.LOGIN_ID = R.LOGIN_ID --- L 表中 LOGIN_ID 唯一索引
AND L.END_TIME IS NULL ----- L 表中 END_TIME is NULL 为 38万
AND R.END_TIME IS NULL ----- R 表中 END_TIME is NULL 为 10 万
AND F.END_TIME IS NULL ----- F 表中 END_TIME is NULL 为 12 万
AND L.USER_ID = USR.USER_ID -- L, USR 表中 USER_ID 存在索引 , USR中是唯一的 。
AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID -- R 中RESPONSIBILITY_ID 无索引(只有56个不同值),RESP_APPL_ID无索引
AND R.RESP_APPL_ID = RSP.APPLICATION_ID ---(只有16个不同值) , RSP 中 APPLICATION_ID与RESPONSIBILITY_ID 联合索引
AND RSP.LANGUAGE = USERENV ('LANG')
AND F.FORM_APPL_ID = FRM.APPLICATION_ID --- F 表FORM_APPL_ID 与FORM_ID 联合索引
AND F.FORM_ID = FRM.FORM_ID --- FRM 表中 FORM_APPL_ID 与FORM_ID 联合唯一索引
AND FRM.LANGUAGE = USERENV ('LANG')
AND F.AUDSID = S.AUDSID --- F 表中AUDSID 有索引
) a
group by a.inst_id ;
其中中间的select 是一个view, 这里把它展开 。
这种SQL 优化如何下手 ? Index scan , full table scan , 表连接 , 还是Cost ??
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-714950/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/35489/viewspace-714950/