SQL execution sequence and writing optimization basis

Each step produces a virtual table that is used as input to the next step. These virtual tables are not available to callers (client applications or external queries). Only the table generated in the last step is returned to the caller. If you do not specify a clause in the query, the corresponding step is skipped.

The parsing order of standard SQL is:
(1). FROM clause, assemble data from different data sources
(2). WHERE clause to filter records based on specified criteria
(3). GROUP BY clause, which divides data into multiple groupings---Group by objects may be generated by aggregate functions (max, MIN, etc.)
(4). Using aggregate functions for calculations
(5). Use the HAVING clause to filter the grouping
(6). Calculate All expressions
(7). Use order by to sort the result set

--Numbers for execution order

(8)SELECT(9)DISTINCT(11)<TopNum><SelectList>
(1)From[Left_table]
(3)<Join_type>JOIN<Right_table>
(2)On<Join_condition>
(4)WHERE<Where_condition>
(5)GROUPBy<Group_by_list>
(6)With <CUBE | RollUP>
(7)having <having_condition>
(ten)ORDER by <order_by_list>

Introduction to the Logical query processing phase

1. from: performs a cartesian product (Cartesian product) (cross join) on the first two tables in the FROM clause, generating a virtual table VT1
2. on: The applies an on filter to the VT1. Only those lines that make <join_condition> true are inserted into the VT2.
3. OUTER (join): If you specify a OUTER join (relative to a cross join or (INNER join), keep the table (preserved Table: Left outer join marks the left table as a reserved table, right outer join marks the right table as a reserved table, a full outer join marks two tables as a reserved table) and no matching rows are found in the VT2 as an outer row. Generate VT3. If the FROM clause contains more than two tables, repeat steps 1 through 3 for the result table and the next table that you generated for the previous join until you have finished processing all the tables.
4. Where: applies a where filter to VT3. Only rows that make <where_condition> true are inserted VT4.
5. GROUP By: generates VT5 by grouping rows in VT4 by the list of columns in the GROUP BY clause.
6. cube| ROLLUP: Inserts a Hyper-group (suppergroups) into VT5, generating VT6.
7. has: has a having filter applied to VT6. Only groups that make
8. Select: processes the select list, producing VT8.
9. DISTINCT: removes duplicate rows from VT8, resulting in VT9.
10. ORDER BY: generates a cursor (VC10) by sorting the rows in VT9 by the list of columns in the ORDER by clause.
11. TOP: selects the specified number or scale of rows from the beginning of the VC10, generates the table VT11, and returns the caller.

Note: Step 10, sort the rows returned by the column list in the ORDER BY clause, and return the cursor VC10. This step is the first and only step you can use the column aliases in the select list. This step differs from the other step in that it does not return a valid table, but instead returns a cursor. SQL is based on the set theory. The collection does not pre-order its rows, it is only a logical collection of members, and the order of the members is irrelevant. A query that sorts a table can return an object that contains rows organized in a specific physical order. ANSI calls this object a cursor. Understanding this step is the basis for a proper understanding of SQL.

Because this step does not return a table (instead of returning a cursor), a query that uses the ORDER BY clause cannot be used as a table expression. Table expressions include: views, inline table-valued functions, subqueries, derived tables, and common expressions. Its result must be returned to the client application that expects to get the physical record.

<sql id= "Get_partake_topics" >
<! [cdata[
Select a.ID, A.name, A.desc, A.topic_pic, A.avatar,max (c.create_time) CTime
From
T_topic A,t_topic_status b,t_status C
where
C.uid =: Uid and c.delflag=1 and c.id=b.status_id and B.topic_id=a.id
Group by a.ID, A.name, A.desc, A.topic_pic, A.avatar
ORDER BY CTime Desc
Fetch First ${num} rows only
]]>
</sql>

1) 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 a right-to-left order, and the FROM clause is written in the final table (the underlying table driving tables) will be processed first, and in the case where the FROM clause contains more than one table, you must select the table with the lowest number of records as the base

The list. If you have more than 3 tables connected to the query, you need to select the crosstab (intersection table) as the underlying table, which refers to the table that is referenced by the other table.
(2) The connection order in the WHERE clause. ：
Oracle uses a bottom-up sequential parsing where clause, according to which the connection between tables must be written before other where conditions, and those that can filter out the maximum number of records must be written at the end of the WHERE clause.

SQL execution sequence and writing optimization basis