Easy understanding of the Where condition execution sequence of Oracle databases

Oracle DatabaseIn today's database market, it is necessary to master the Oracle database. It is also a strong condition for you to master the Where condition execution sequence of the Oracle database.

Because SQL optimization is complex and restricted by the Environment, you must follow the following principles to write SQL during development:

1. ORACLE uses bottom-up sequence to parse WHERE clausesAccording to this principle, the join between tables must be written before 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' 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.

Note: Column ambiguity means 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.
 

More statement conditions are involved in the Oracle database. This article describes the Where condition execution sequence. You can learn more about the statement conditions in the Oracle database, I hope the content described above will be helpful to you.