GreenPlum和Oracle where执行顺序

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

1. 表关联时，是先做过滤，再做关联；
2. 在条件过滤时，优先过滤与字段相关的条件，然后过滤与字段不相关的条件，并且都是从左往右过滤；
3. sql优化：能过滤较多数据并且计算较少的条件尽量放在靠近where，计算较多的条件尽量放在远离where；

Oracle

1. 表关联时，是先做过滤，再做关联；
2. 在条件过滤时，优先过滤与字段不相关的条件，并且与字段不相关的条件是从右往左依次过滤；然后再过滤与字段相关的条件，并且与字段相关的条件是从左往右依次过滤；
3. sql优化：条件中包含字段时，能过滤较多数据并且计算较少的条件尽量放在靠近where，计算较多的条件尽量放在远离where；条件中不包含字段时，真条件尽量靠近where，假条件尽量远离where。