Whether the order of Where clauses in oracle affects SQL Performance

 

It is often asked whether the conditional writing sequence of the Where clause in oracle has an impact on SQL Performance. My intuition is that if this order has an impact, Oracle should have been able to achieve automatic optimization for a long time, however, there has been no conclusive evidence in this regard. Articles found on the Internet generally think that there is no impact in the RBO optimizer mode (the default RBO optimizer mode starts from 10 Gb), but there is an impact 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 the execution of 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.

 

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

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;

 

1. select * from temp where to_number (t2)> 1 and t1 = 'sz ';

2. 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 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 SQL conditions is from right to left.

Then, based on the analysis of this result, we can put the conditions with the least results on the rightmost side. Will it reduce the number of records used for execution of other conditions and thus improve performance?

For example, should I adjust the order of the following SQL conditions?

 

Where A. Checkout id Is Not Null

And A. Record status <> 0

And A. Billing fee = 1

And (Nvl (A. Actual amount, 0) <> Nvl (A. Checkout amount, 0) Or Nvl (A. Checkout amount, 0) = 0)

And A. Patient ID = [1] And Instr ([2], ',' | Nvl (A. Homepage ID, 0) | ',')> 0

And A. Registration time Between [3] And [4]

And A. Outpatient indicator <> 1

 

In fact, from the execution plan of this SQL statement, Oracle will first find the conditions for using indexes or table connections in the conditions to filter the dataset, check the records involved in these result data blocks one by one to check whether all conditions are met. Therefore, the conditional order has almost no impact on the performance.

If there is no connection between indexes and tables, does the order of conditions affect performance? Let's take a look at an experiment.

 

 

Experiment 4: It is proved that the order of conditions has no effect on the performance.

 

SQL> select count (*) from medical project directory where operation type = '1'; COUNT (*)

----------

3251

 

SQL> select count (*) from medical project directory where category = 'Z'; COUNT (*)

----------

170

 

SQL> select count (*) from medical project directory where category = 'Z' and operation type = '1'; COUNT (*)

----------

1

 

Declare

V1 Varchar2 (20 );

Begin

For I In 1 .. 1000 Loop

-- Select name Into V1 From medical project directory Where category = 'Z' And operation type = '1 ';

Select name Into V1 from medical project directory where operation type = '1' and Category = 'Z ';

End Loop;

End;

/

 

The preceding SQL statement executes 1000 queries in two ways. The results are as follows:

Type = '1' at rightmost | Category = 'Z' at rightmost

0.093 | 1.014

1.06 | 0.999

0.998 | 1.014

 

It is reasonable to say that, in the order from right to left, when "Category = 'Z'" is on the far right, 170 records are first filtered out, find the "operation type = '1'". In comparison, when the "operation type = '1'" is on the rightmost side, filter out the first 3251 records, find the "Category = 'Z'" from it, and the efficiency should be lower, but the actual result is that the time for the two is almost the same.

In fact, from the analysis of Oracle's data access principle, the execution plans of the two sequential statements are the same, they are all full table scans, and all data blocks of the table must be accessed in sequence, check whether the rows in each data block meet both conditions one by one. Therefore, there is no issue of how many pieces of data are filtered out first.

 

To sum up, the order of conditions in the Where clause has no effect on the performance (whether it is the CBO or RBO optimizer mode). Note that the order of conditions is described here, does not contain the order of the table. In the RBO optimizer mode, tables should be arranged from left to right in the order of the number of results records from large to small, because when tables are connected, the rightmost table is placed in the outermost layer of the nested loop. The lower the number of loops on the outermost layer, the higher the efficiency.

 

From the artist's blog