Oracle where condition execution sequence

SQL optimization is complex and restricted by the environment. However, the minimum principle for writing SQL statements during development is as follows:

1. oracle uses the bottom-up sequence to parse the WHERE clause. According to this principle, the join between tables must be written before other where conditions. 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' and 25 <(select count (*) from EMP where Mgr = E. empno );

(Efficient)

Select... From EMP e where 25 <(select count (*) from EMP where Mgr = E. empno) and Sal> 50000 and job = 'manager ';

2. Avoid '*' in the select clause '*'

When all columns are listed in the select clause, it is convenient to reference '*' using dynamic SQL columns. however, this is a very inefficient method. in fact, Oracle converts '*' into all column names in sequence during parsing. This task is done by querying the data dictionary, which means it takes more time.

3. Use the table alias (alias)

When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each column. in this way, the parsing time can be reduced and the syntax errors caused by column ambiguity can be reduced.

(Column ambiguity refers to the fact that different SQL tables have the same column name. When this column appears in an SQL statement, the SQL parser cannot determine the attribute of this column)