select c.name,a.prod_id from table_1@dl_link a, table_2@dl_link b, table_3@dl_link c where a.prod_id=b.prod_id and substr(a.area_id,1,3)||'00'=c.area_id and b.start_dt<sysdate-90and b.prod_status_cd='2' and b.status_cd='12' and a.redu_prod_spec_id='379' and a.prod_id in ( select d.prod_id from inst.offer_prod_status@dl_newedw_crmdb1 d where d.prod_status_cd='1' and d.status_cd='12') 最后的in的前提是offer_prod_status这个表的数据量小,具体优化还要看执行计划
from table_1@dl_link a,
table_2@dl_link b,
table_3@dl_link c
where a.prod_id=b.prod_id
and substr(a.area_id,1,3)||'00'=c.area_id
and b.start_dt<sysdate-90and b.prod_status_cd='2'
and b.status_cd='12'
and a.redu_prod_spec_id='379'
and a.prod_id in (
select d.prod_id from inst.offer_prod_status@dl_newedw_crmdb1 d
where d.prod_status_cd='1'
and d.status_cd='12')
最后的in的前提是offer_prod_status这个表的数据量小,具体优化还要看执行计划
1. AND B.START_DT < SYSDATE - 90--AND SYSDATE - B.START_DT > 90
2. IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况
然后建立对应的索引就差不多了