Lets delve into the Logical Query Processing Phases, below given is the general form of query, along with the steps according to the order in which different clause are logically processed.
(8) SELECT (9) DISTINCT (11) TOP
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_codition>
(10) ORDER BY <order_by_list>
In most of the programming language the code is executed in the order in which it is writer. In SQL, the first clause that is processed is the FROM clause, while the SELECT clause which appears first processed almost last.
Each step in query processing generates VIRTUAL TABLE that is used as the input for the next step to process. These virtual tables are not available to the caller. Only the table generated by the final step is returned to the caller.
Brief on various phases of Logical Query Processing
(1) FROM: A Cartesian product(cross join) is performed between the tables in the FROM clause, and as a result a virtual table gets generated (VT1).
(2) ON: The ON filter is applied to VT1. Only rows for which the join condition matches or / for which the join condition is true are inserted to form VT2.
(3) OUTER (join): If outer join is specified, matching rows from the VT1 along with all the rows of VT2 are picked up and inserted to form VT3.
(4) WHERE: The where filter is applied to VT3. Only rows for which the <where_condition> is evaluate to TRUE are inserted to form VT4.
(5) GROUP BY: The rows from the VT4 are arranged by groups based on the column list specified in the GROUP BY Clause. VT5 is generated.
(6) CUBE | ROLLUP: Subgroups are generated and added to the rows of VT5 to form VT6.
(7) HAVING: The having filters are applied to VT6. Only groups for which the <having_condition> is true are inserted to VT7.
(8) SELECT: The select is performed on VT7 to generate VT8.
(9) DISTINCT: Duplicate rows are removed from VT8 to for VT9.
(10)ORDER BY: The rows from the VT9 are sorted according to the column list specified in the ORDER BY clause to form VT10
(11)TOP: The specified number or percentage of rows are selected from top to form VT11.
And this VT11 is return to the caller.
In next post I will explain phases of query processing taking an example. By then Have gr8 coding. J
No comments:
Post a Comment