Скачать или смотреть SQL: What is Oracle Logical Query Processing Order?

****How ORACLE process the SQL Query?
****What is the SQL Query Logical processing order?
****What is ORACLE SQL Query order of operations?

(1) Server Process Check

Which type of query it is - SQL or PL/SQL?
If SQL, then server process puts the code in SQL Area available in library cache under shared pool.
If PL/SQL, the server process put SQL code in SQL area and PL code in PL area in library cache under shared pool.


(2) Parsing

Syntax check if all the keywords used in your Query are valid
Sementic check will check all the tables are spelled correctly and available in Data Dictionary.

(3) Transformation and Query Analyzing

Look for the source tables (From Clause)
Relationship between tables (Join)
Eliminate rows based on the filters (Where Clause)
Group the remaining rows and Transform all calculated fields for each category(Group By Clause)
Eliminate Groups based on having condition(Having Clause)
Select the resultset for your Query result window(Select Clause)
Order the remaining reocrds usign Order By in whichever order you want to sor your data.


(4) Estimation
Query estimation by analyzing different operator used in a Query and their cost.


(5) Plan Generation
Use the existing plan
Generate tables stats and new plan based on the latest statistics


(6) Execution
Based on the Execution plan and data requested in our Query,
Server process is going to pick the data from Buffer cache if it is available
If not then server process will read the Data Block from Data Files - will put the data block into Buffer cache
Data will be displayed to the user