GreenPlum
样例准备
create or replace function test_1()
returns varchar
language plpgsql
as $function$
begin
raise notice '1';
return '1';
end;
$function$
;
create or replace function test_2()
returns varchar
language plpgsql
as $function$
begin
raise notice '2';
return '2';
end;
$function$
;
create or replace function test_3()
returns varchar
language plpgsql
as $function$
begin
raise notice '3';
return '3';
end;
$function$
;
create or replace function test_4()
returns varchar
language plpgsql
as $function$
begin
raise notice '4';
return '4';
end;
$function$
;
drop table tmp;
create table tmp as
select '1'::varchar id1,'2'::varchar id2
distributed by (id1);
测试用例1
select 1 from tmp a, tmp b
where a.id1||test_1() = b.id1||test_1()
and a.id2 = test_2()
and test_3() = '3'
and test_4() = '4'
and a.id1 = test_1();
- 结果:输出 2 1 3 4 1 1
测试用例2
select 1 from tmp a, tmp b
where a.id1||test_1() = b.id1||test_1()
and a.id2 = test_2()
and test_3() = '3'
and test_4() = '4'
and a.id1 = test_1()
and a.id2 = test_1();
- 结果:输出 2 1 1
Oracle
样例准备
create or replace function test_1
return varchar2
as
begin
dbms_output.put_line('1');
return '1';
end;
/
create or replace function test_2
return varchar2
as
begin
dbms_output.put_line('2');
return '2';
end;
/
create or replace function test_3
return varchar2
as
begin
dbms_output.put_line('3');
return '3';
end;
/
create or replace function test_4
return varchar2
as
begin
dbms_output.put_line('4');
return '4';
end;
/
drop table tmp;
create table tmp as
select '1' id1,'2' id2 FROM dual;
测试用例1
select 1 from tmp a, tmp b
where a.id1||test_1() = b.id1||test_1()
and a.id2 = test_2()
and test_3() = '3'
and test_4() = '4'
and a.id1 = test_1();
- 结果:输出 4 3 2 1 1 1
测试用例2
select 1 from tmp a, tmp b
where a.id1||test_1() = b.id1||test_1()
and a.id2 = test_2()
and test_3() = '3'
and test_4() = '4'
and a.id1 = test_1()
and a.id2 = test_1();
- 结果:输出 4 3 2 1 1
结论
GreenPlum
- 表关联时,是先做过滤,再做关联;
- 在条件过滤时,优先过滤与字段相关的条件,然后过滤与字段不相关的条件,并且都是从左往右过滤;
- sql优化:能过滤较多数据并且计算较少的条件尽量放在靠近where,计算较多的条件尽量放在远离where;
Oracle
- 表关联时,是先做过滤,再做关联;
- 在条件过滤时,优先过滤与字段不相关的条件,并且与字段不相关的条件是从右往左依次过滤;然后再过滤与字段相关的条件,并且与字段相关的条件是从左往右依次过滤;
- sql优化:条件中包含字段时,能过滤较多数据并且计算较少的条件尽量放在靠近where,计算较多的条件尽量放在远离where;条件中不包含字段时,真条件尽量靠近where,假条件尽量远离where。