如何优化下面的DDL 语句
--各个表的数据量
SELECT COUNT(1) FROM table_1@dl_link --38554490
SELECT COUNT(1) FROM table_2@dl_link --207854318
SELECT COUNT(1) FROM table_3@dl_link --218
create table temp_table
as
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 sysdate-b.start_dt>90
and b.prod_status_cd='2'
and b.status_cd='12'
and a.redu_prod_spec_id='379'
and exists (
select 1 from inst.offer_prod_status@dl_newedw_crmdb1 d
where a.prod_id=d.prod_id
and d.prod_status_cd='1'
and d.status_cd='12')
------解决思路----------------------
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这个表的数据量小,具体优化还要看执行计划
------解决思路----------------------
sql上已经很优了,注意两点:
1. AND B.START_DT < SYSDATE - 90--AND SYSDATE - B.START_DT > 90
2. IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况
然后建立对应的索引就差不多了