Detailed _oracle of Oracle Select execution Order

SQL SELECT statement Complete order of execution:
1. The FROM clause assembles data from different data sources;
2. The WHERE clause filters the record rows based on the specified criteria;
3. The GROUP BY clause divides the data into multiple groupings;
4, using aggregation function to calculate;
5, using the HAVING clause to filter groupings;
6, the calculation of all expressions;
7. Sort the result set by using order by.

methods of improving query efficiency with Oracle statements
1:. Where column in (SELECT * from ... where ...);
2:. Where exists (select ' X ' from ... where ...);
The second format is far more efficient than the first format. Almost all in-operation subcode queries can be rewritten in Oracle to use exists subqueries
Using the Exists,oracle system first checks the main query and then runs the subquery until it finds the first match, which saves time
When an Oracle system executes an in subquery, it executes the subquery first and stores the resulting list in an indexed temporary table.

Select the most efficient table name order (valid only in the Rule-based optimizer)
The Oracle parser processes the table names in the FROM clause in Right-to-left order, so the last table in the FROM clause (the underlying table driving tables) is processed first. In the case where multiple tables are included in the FROM clause, you must select the table with the least number of records as the underlying table. When Oracle processes multiple tables, it uses sorting and merging to connect them. First, scan the first table (the last table in the FROM clause) and dispatch the records, then scan the second table (the last second table in the FROM clause), and finally merge all the records retrieved from the second table with the appropriate records in the first table.
If you have more than 3 table join queries, you need to select the Crosstab table (intersection table) as the underlying table, which is the table referenced by the other tables.

Join order in the WHERE clause
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.