select PF_RECIPIENT_LIST_HD_ID,SENDER_USERID,CREATION_DATE from PF_RECIPIENT_LIST_HD b where exists (
select 1 from user_mt a, user_permission c where a.userid (+)= c.userid and and a.userid<>'8001zhaolu' and a.USERID=b.SENDER_USERID and c.project_id = 11105 and c.permission_code = 'MYPROJECT' and c.active_flag = 'Y' and c.allow_view = 'Y' )
and not exists (
select 1 from PF_RECIPIENT_LIST_DT d where exists (
select 1 from PF_USER_INBOX e where e.PF_RECIPIENT_LIST_DT_ID=d.PF_RECIPIENT_LIST_DT_ID and e.PROJECT_ID = 11105)
and b.PF_RECIPIENT_LIST_HD_ID=d.PF_RECIPIENT_LIST_DT_ID)
and RELATED_TABLE='PF_FILE_CORRESPONDENCE' and FILE_ID=33064 and DISTRIBUTION_TYPE='E'
order by CREATION_DATE desc另外部分条件还需要根据具体表的数据量与分布来定,此外直接关联也许会更快,但不清楚表的数据量有多大!
select 1 from user_mt a, user_permission c where a.userid (+)= c.userid and and a.userid<>'8001zhaolu' and a.USERID=b.SENDER_USERID and c.project_id = 11105 and c.permission_code = 'MYPROJECT' and c.active_flag = 'Y' and c.allow_view = 'Y' )
and not exists (
select 1 from PF_RECIPIENT_LIST_DT d where exists (
select 1 from PF_USER_INBOX e where e.PF_RECIPIENT_LIST_DT_ID=d.PF_RECIPIENT_LIST_DT_ID and e.PROJECT_ID = 11105)
and b.PF_RECIPIENT_LIST_HD_ID=d.PF_RECIPIENT_LIST_DT_ID)
and RELATED_TABLE='PF_FILE_CORRESPONDENCE' and FILE_ID=33064 and DISTRIBUTION_TYPE='E'
order by CREATION_DATE desc另外部分条件还需要根据具体表的数据量与分布来定,此外直接关联也许会更快,但不清楚表的数据量有多大!
b表N条,a,c表M条,d表20N条,e表20N条其中M<<N,a,c是系统注册表,数据相对固定,不会太多,b,d,e是数据表,用户经常使用,数据量很庞大,还有什么办法优化? 呵呵,顺便能不能说明一下优化的原因?