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