What are the impact of Oracle SQL condition sequence on performance?

This article mainly describes the impact of the actual sequence of Oracle SQL conditions on relevant performance. In practice, someone will ask whether the correct Where clause condition writing sequence in Oracle Database has an impact on SQL Performance. My intuition is that it has no impact.

If this order has an impact, Oracle should have been able to achieve automatic optimization for a long time, but there is no conclusive evidence of this. Articles found on the Internet generally think that the RBO optimizer mode does not affect the start time of 10 Gb, and the default is the RBO optimizer mode), but in the CBO optimizer mode, there are two main points of view:

A. Place the conditions with the least results on the rightmost side, and filter the result set from right to left for SQL Execution;

B. Some tests have shown that the leftmost condition can be the least result, and the SQL Performance is higher.

I have checked the online documents from oracle8 to 11G. I have not read any documents about SQL optimization. Which of the following statements is true if the conditions in the where clause affect SQL Performance, there was no definite conclusion, so I had to do the experiment on my own. The results show that we all know that the execution of Oracle SQL conditions is from right to left, but the order of conditions has no effect on SQL Performance.

Experiment 1: proves that SQL syntax analysis is from right to left.

The following test results are the same for both 9i and 10G: the execution of 1st statements will not go wrong, and the 2nd statements will prompt that the divisor cannot be zero.

 
 

  
  
Select 'ok' From Dual Where 1 / 0 = 1 And 1 = 2;  
  
  
Select 'ok' From Dual Where 1 = 2 And 1 / 0 = 1;  
 
 

It proves that SQL syntax analysis is from right to left.

Experiment 2: demonstrate that SQL condition execution is from right to left.

 
 

  
  
drop table temp;  
  
  
create table temp( t1 varchar2(10),t2 varchar2(10));  
  
  
insert into temp values('zm','abcde');  
  
  
insert into temp values('sz','1');  
  
  
insert into temp values('sz','2');  
  
  
commit;  
  
  
select * from temp where to_number(t2)>1 and t1='sz';  
  
  
select * from temp where t1='sz' and to_number(t2)>1;  
 
 

If you run 1st statements on 9i, no errors will occur. If you run 2nd statements, the system prompts "invalid number"

If you run the two statements on 10 Gb, no error occurs.

Note: On 9i, the execution of Oracle SQL conditions is indeed from right to left, but what adjustments have been made for 10 Gb?

Experiment 3: it proves that the execution of SQL conditions on 10 Gb is from right to left.

 
 

  
  
Create Or Replace Function F1(v_In Varchar2) Return Varchar2 Is  
  
  
Begin  
  
  
Dbms_Output.Put_Line('exec F1');  
  
  
Return v_In;  
  
  
End F1;  
  
  
/  
  
  
Create Or Replace Function F2(v_In Varchar2) Return Varchar2 Is  
  
  
Begin  
  
  
Dbms_Output.Put_Line('exec F2');  
  
  
Return v_In;  
  
  
End F2;  
  
  
/  
  
  
SQL> set serverout on;  
  
  
SQL> select 1 from dual where f1('1')='1' and f2('1')='1';  
  
  
1  
  
  
1  
  
  
exec F2  
  
  
exec F1  
  
  
SQL> select 1 from dual where f2('1')='1' and f1('1')='1';  
  
  
1  
  
  
1  
  
  
exec F1  
  
  
exec F2  
 
 

The results show that the execution sequence of Oracle SQL conditions is from right to left.