How ORACLE process the SQL Query? What is the SQL Query Logical Processing Order?
Consider below Query clauses and understand how they get processed when executed in a form of a SQL Query:
So, calculated SELECT columns or alias will not be available in the WHERE clause, but they will be available in the ORDER BY clause. Because the SELECT clause executed right beforehand, everything from the SELECT should be available at the time of ORDER BY execution.
Also, SELECT columns are not referenceable in the WHERE clause. If you create a column in the SELECT directive. It will not be available in the WHERE clause because the SELECT clause has not even been executed at the time the WHERE clause is being run.
When we pass, a SELECT query it executes under some phases. That includes
1) Checking by Server Process – Which type of Query is this – SQL or Pl/SQL.
2) If SQL – server process puts the code in the SQL area available under the library cache under the shared pool.
3) If pl/SQL – server process puts the SQL code in the SQL area and pl code in pl area available under the library cache area under the shared pool.
4) Now Parsing (Syntax, Privileges on user passing the query checks)
5) Check if the execution plan is already available or not.
6) if not creates an execution plan on behalf of available statistics, if yes, uses the available execution plan.
7) checks if the data blocks contain required data are available in buffer cache or not.
8) if yes, the server process picks the data from BC and returns to the user. If not server process reads the data blocks from data files and puts the data blocks into the buffer cache and returns to the user.