30 June 2009

Oracle SQL query execution hiearchy

When I was working yesterday, I found the execution hiearchy of execution of SQL statements

SELECT name,deptno
FROM employee
WHERE ID NOT EXISTS(SELECT ID FROM Bank_Customers)
GROUP BY Deptno;

Sequence of Execution
1. FROM Clause
2. WHERE Condition
3. GROUP Clause
4. HAVING Clause (You DBA advises to filter data in WHERE clause I Suppose)
5. SELECT
6. ORDER Clause

  • FROM - It fetch all the Data from the respective tables
  • WHERE - It applies the data filter using the WHERE clause
  • GROUP - The Group clause is called to Group Data
  • HAVING - The Having clause applies data filter to the Grouped Data,When you apply the filter at this stage the performance decreases, So try to filter the maximum at the WHERE clause level itself
  • SELECT - The Select Clause selects the required columns (Thus Choosing one column or two Column does not have much impact on Database performance.But you should not apply '*' to retrieve all the rows as the huge amount of data leads to Network traffic and the performance of your application becomes slow.
  • ORDER - Then the ORDER lause sorts the data and then gives back to the user to be shown in the application.


*************************************************************************************

Kindly correct me if Iam wrong by leaving a comment or please drop me a mail to Subramanian.kaushik@hotmail.com