What are the effects of SQL where condition order on performance

It is often asked whether the conditional writing order of the WHERE clause in Oracle has an impact on SQL performance, and my intuition is not affected, because if this order has an impact, Oracle should be able to automatically optimize it early, But there has been no conclusive evidence of this. articles found on the Internet are generally considered to have no impact in the Rbo Optimizer mode (starting with 10G, the default is the Rbo optimizer mode), and in the CBO optimizer mode, there are two main points of view:

A. The condition that minimizes the results is placed on the far right, and SQL execution is filtered from right to left for the result set;

B. Some experiments have shown that the conditions with the fewest results can be left on the leftmost and SQL performance is higher.

Checked the online documentation for Oracle8 to 11G, for a section on SQL optimization, there was no document saying that the conditions in the WHERE clause had an effect on the performance of SQL, and that there was no definite conclusion that the argument had to be proved by itself. The results show that the execution of the SQL condition is right-to-left, but the order of the conditions has no effect on SQL performance.

Experiment one: It proves that the syntax analysis of SQL is right-to-left

The following experiment can achieve the same result in both 9i and 10G: The 1th statement executes without error, and the 2nd statement indicates 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 is proved that the parsing of SQL is right-to-left.

Experiment two: It proves that the execution of SQL condition is right-to-left

drop table temp; CREATE TABLE Temp (T1 VARCHAR2 (Ten), 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;

Executes on 9i, the 1th statement executes without error, and the 2nd statement prompts "Invalid number"

On 10G, two statements do not go wrong.

Description: On 9i, the execution of the SQL condition is really right-to-left, but what is the 10G tuning?

Experiment three: proves that the execution of SQL conditions on 10g is 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 order of SQL conditions is right-to-left.

So, based on this result, can you put the condition with the least amount of results on the far right, reduce the number of records used when other conditions are executed, and improve performance?

For example: The following SQL condition, should you adjust the order of the SQL conditions?

Where A. Checkout ID is not Null and A. Record status <>0 and A. Billing Fee =1 and (NVL (a. Paid amount, 0) <>NVL (A. Checkout amount, 0) Or NVL (A. Checkout amount, 0) =0) and A. Patient id=[1] and INSTR ([2], ', ' | | NVL (A. Home id,0) | | ', ') >0 and A. Registration time between [3] and [4] and A. Outpatient Sign <>1

In fact, from the execution plan of this SQL statement, Oracle will first identify conditions in which indexes or inter-table joins are used to filter the data set, and then check each of the records involved in these result blocks to see if all the conditions are met, so the condition order has little effect on performance.
If there is no index and inter-table connection, does the order of conditions affect performance? Look at an experiment again.

Experiment four: It proves that the order of conditions has no effect on performance.

Sql> Select COUNT (*) from diagnostic project directory where operation type = ' 1 '; COUNT (*) ---------- 3251 Sql> Select COUNT (*) from diagnostic project directory where category = ' Z '; COUNT (*) ---------- 170 Sql> Select COUNT (*) from diagnostic project directory where category = ' Z ' and operation type = ' 1 '; COUNT (*) ---------- 1 Declare V1 Varchar2 (20); Begin For I in 1.. 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 above SQL performs 1000 queries in two different ways, with the following results:

Operation type = ' 1 ' on the right side | category = ' Z ' on the right

0.093 | 1.014

1.06 | 0.999

0.998 | 1.014

Supposedly, execution from right to left, "category = ' Z '" at the far right, first filtered to get 170 records, and then found in accordance with "operation type = ' 1 '", compared, "operation type = ' 1 '" at the far right, first filtered to get 3,251 records, and then found in accordance with "category = ' Z '", Efficiency should be lower, but the actual result is the same time.

In fact, from the Oracle data access principle to analyze, the two order of writing, the execution plan is the same, all the table scan, all of the table to access all the data blocks, each block of rows, check whether the same two conditions are met. Therefore, there is no problem of filtering out the number of data first.

　　in summary, the order of the conditions in the WHERE clause has no effect on performance (either the CBO or the Rbo optimizer mode). Note that, in addition, this is just the order of the conditions, not the order of the tables. In Rbo optimizer mode, tables should be arranged from left to right in the order in which the number of results records is from the largest to the highest, because the rightmost table is placed at the outermost end of the nested loop when the table is connected. The lower the number of loops in the outermost layer, the higher the efficiency.

What are the effects of SQL where condition order on performance