Oracle database where condition execution order and the condition order of WHERE clause affect performance

1. Oracle database Where Condition execution order:

Because SQL optimization is more complex and is subject to environmental constraints, writing SQL must follow the following guidelines during development:

1.ORACLE parses the WHERE clause in a bottom-up order, according to which the connection between the tables must be written before the other where conditions, and the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

For example:

(inefficient)

SELECT ... From emp E WHERE SAL > 50000 and JOB = ' MANAGER ' < (SELECT COUNT (*) from EMP where mgr=e.empno);

Efficient

SELECT ... From EMP E where < (SELECT COUNT (*) from EMP where mgr=e.empno) and SAL > 50000 and JOB = ' MANAGER ';

avoid the use of ' * ' in the 2.SELECT clause

Using dynamic SQL column references ' is a convenient way to list all columns in the SELECT clause. However, this is a very inefficient method. In fact, Oracle will "convert" to all column names in the parsing process, which is done by querying the data dictionary, which means more time will be spent.

3. Use table alias (alias)

When you connect multiple tables in an SQL statement, use the alias of the table and prefix the alias with each column. This allows you to reduce parsing time and reduce syntax errors caused by column ambiguity.

Note: column ambiguity refers to the fact that because different tables in SQL have the same column name, the SQL parser cannot determine the attribution of this column when the column appears in the SQL statement. 2, the effect of the conditional order of the WHERE clause in Oracle on performance:

It is often asked whether the conditional write order of the WHERE clause in Oracle has an effect on SQL performance, and my intuition has no effect, because if this order has an impact, Oracle should be able to automate optimizations early, but there is no conclusive evidence of this. Articles found on the web are generally considered to have no impact in the Rbo optimizer model (10G, default for Rbo optimizer mode), and in the CBO optimizer model, there are two main points of view:

A. The conditions that minimize the results are placed at the far right, and SQL execution is filtered by the result set from right to left;

B. Experiments have shown that the conditions that minimize the results are placed at the far left, with a higher SQL performance.

After checking the online documentation for Oracle8 to 11G, about the SQL optimization related chapters, there is no document saying that the conditions in the WHERE clause have an effect on SQL performance, which is right, without a definitive conclusion, and has to do the experiment to prove it. The results show that SQL conditions are executed from right to left, but the order of conditions has no effect on SQL performance.

Experiment one: It is proved that the parsing of SQL is from right to left

The following experiment can get the same result in 9i and 10G: The 1th statement executes without error, and the 2nd statement prompts 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 from right to left.

Experiment two: It is proved that the execution of SQL condition is from right to left

drop table temp; 
CREATE TABLE Temp (T1 varchar2 (), T2 varchar2); 
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"

Executes on 10G, two statements are not faulted.

Description: 9i, the execution of the SQL conditions is really from right to left, but 10G did what adjustment?

Experiment three: Prove that the execution of SQL conditions on 10g is from right to left

The Create Or Replace Function F1 (v_in Varchar2) return Varchar2 the 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 from right to left.

So, based on this result, can you improve performance by putting the lowest-performing conditions on the far right, and reducing the number of records that are used when other conditions are executed?

For example: The following SQL condition, should the order of the SQL conditions be adjusted?

Where A. Checkout ID is not Null and

a. Record status <>0

and a. Accounting costs =1

and (NVL (A. Paid-in amount, 0) <>NVL (A. Checkout amount, 0) Or N VL (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 signs <>1

In fact, from the execution plan for this SQL statement, Oracle first discovers the criteria for using indexes or table joins to filter the dataset, and then checks whether the records involved in these result blocks meet all the criteria, so the conditional order has little effect on performance.

The Create Or Replace Function F1 (v_in Varchar2) return Varchar2 the 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 from right to left.

So, based on this result, can you improve performance by putting the lowest-performing conditions on the far right, and reducing the number of records that are used when other conditions are executed?

For example: The following SQL condition, should the order of the SQL conditions be adjusted?

Where A. Checkout ID is not Null and

a. Record status <>0

and a. Accounting costs =1

and (NVL (A. Paid-in amount, 0) <>NVL (A. Checkout amount, 0) Or N VL (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 signs <>1

In fact, from the execution plan for this SQL statement, Oracle first discovers the criteria for using indexes or table joins to filter the dataset, and then checks whether the records involved in these result blocks meet all the criteria, so the conditional order has little effect on performance.