Effect of SQL condition order on Performance

People often ask Whether the conditional writing sequence of the where clause in Oracle has an impact on SQL performance does not affect my intuition, because if the 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. Found on the InternetArticleGenerally, it is considered that there is no impact in the RBO optimizer mode (the default is the RBO optimizer mode at the beginning of 10 Gb), but there are two main points of view in the CBO optimizer mode:

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

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

CheckedOracle 8 to 11g online documentation, about SQL optimization related chapter, no documentation said that the conditions in the WHERE clause have an impact on SQL Performance, which of the following points is true, 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: provesSQL syntax analysis is from right to left

The following experiments are conducted inThe same result can be obtained for 9i and 10 GB: 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;

ProvedSQL syntax analysis is from right to left.

 

Experiment 2: provesSQL 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;

InIf the code is executed on 9i, the execution of 1st statements will not go wrong, and the 2nd statements will prompt "Invalid Number"

InThe two statements are executed at 10 Gb without errors.

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: proves thatThe 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 result shows 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 exampleShould I adjust the order of SQL conditions?

Where.Checkout ID is not null

And.Record status <> 0

And.Billing fee = 1

And (nvl (.Amount received, 0) <> nvl (A. amount settled, 0) or nvl (A. amount settled, 0) = 0)

And.Patient id = [1] and instr ([2], ',' | nvl (A. Homepage ID, 0) | ',')> 0

And.Registration Time: between [3] and [4]

And.Outpatient indicator <> 1

In fact, from thisThe execution plan of the SQL statement is analyzed. Oracle first finds 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 (*) fromWhere operation type = '1 ';

Count (*)

----------

3251

SQL> select count (*) fromWhere Category = 'Z ';

Count (*)

----------

170

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

Count (*)

----------

1

Declare

V1 varchar2 (20 );

Begin

For I in 1 .. 1000 Loop

-- SelectName into V1 from medical project directory where category = 'Z' and operation type = '1 ';

SelectName into V1 from medical project directory where operation type = '1' and Category = 'Z ';

End loop;

End;

/

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

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

0.093 | 1.014

1.06 | 0.999

0.998 | 1.014

 

It is reasonable to say that the class is executed in the order from right to left.When 'Z' is on the rightmost side, filter out the first 170 records and find the records that match the "operation type = '1'". In comparison, when "operation type = '1'" is on the rightmost side, filter out the first 3251 records and find the records that match "Category = 'Z'". The efficiency should be lower, the actual result is that the time is almost the same as that of the two.

In factThe Data Access Principle of Oracle is analyzed. 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.

In conclusion, the sequence of conditions in the where clause does not affect the performance (whether in CBO or RBO optimizer mode, here, we only talk about the order of conditions, excluding the order of tables. 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.