在测试中发现,在GP中,部分使用IN语法的sql,执行起来比较慢。要是发现有类似情况,可以优化一下,改为exist 或者not exist语法
例如
insert into my_db.my_table1
sel
t2.field1
,t2.field2
,t2.field3
,t2.field4
,t2.field5
,t3.field6
,2
,t3.field7
from
my_db.table2 t2
inner join
my_db.table3 t3
on t2.field4=t3.field4
and t2.field8=t3.field9
and t2.field10=t3.field11
and t2.field12=t3.field13
and t2.field14=t3.Tkg_field14
and t3.field6>=0
and t3.field15=1
and t3.field16=1
where
( t2.field1,t2.field2,t2.field3 ) not in (sel field1 ,field2 ,field3 from my_db.my_table1 )
and
t3.field4 not in (sel field4 from my_db.my_table1 group by 1) ;
修改为:
insert into my_db.my_table1
select
t2.field1
,t2.field2
,t2.field3
,t2.field4
,t2.field5
,t3.field6
,2
,t3.field7
from
my_db.table2 t2
inner join
my_db.table3 t3
on t2.field4=t3.field4
and t2.field8=t3.field9
and t2.field10=t3.field11
and t2.field12=t3.field13
and t2.field14=t3.Tkg_field14
and t3.field6>=0
and t3.field15=1
and t3.field16=1
where not exists (select * from my_db.my_table1 aaaa where aaaa.field1=t2.field1 and aaaa.field2=t2.field2 and aaaa.field3=t2.field3 )
and not exists (select * from my_db.my_table1 aaaa where aaaa.field4=t2.field4 )
;
例如
insert into my_db.my_table1
sel
t2.field1
,t2.field2
,t2.field3
,t2.field4
,t2.field5
,t3.field6
,2
,t3.field7
from
my_db.table2 t2
inner join
my_db.table3 t3
on t2.field4=t3.field4
and t2.field8=t3.field9
and t2.field10=t3.field11
and t2.field12=t3.field13
and t2.field14=t3.Tkg_field14
and t3.field6>=0
and t3.field15=1
and t3.field16=1
where
( t2.field1,t2.field2,t2.field3 ) not in (sel field1 ,field2 ,field3 from my_db.my_table1 )
and
t3.field4 not in (sel field4 from my_db.my_table1 group by 1) ;
修改为:
insert into my_db.my_table1
select
t2.field1
,t2.field2
,t2.field3
,t2.field4
,t2.field5
,t3.field6
,2
,t3.field7
from
my_db.table2 t2
inner join
my_db.table3 t3
on t2.field4=t3.field4
and t2.field8=t3.field9
and t2.field10=t3.field11
and t2.field12=t3.field13
and t2.field14=t3.Tkg_field14
and t3.field6>=0
and t3.field15=1
and t3.field16=1
where not exists (select * from my_db.my_table1 aaaa where aaaa.field1=t2.field1 and aaaa.field2=t2.field2 and aaaa.field3=t2.field3 )
and not exists (select * from my_db.my_table1 aaaa where aaaa.field4=t2.field4 )
;