Sequence of SQL statement processed

When a query is submitted to the database, it is executed in the following order:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

The following diagram will show a clear picture:

How to use table indexes for better performance

Table index is one of the most important areas, while optimizing a query.

Here is the best practice of using these indexes, to get the optimal performance gain.


1. Consider creating indexes 'only' on those columns which are frequently used in WHERE, ORDER BY and GROUP BY clauses.

2. Do not create duplicate or un-necessary indexes. These indexes hampers insert, delete and update performance of any query. Consider dropping any such indexes, if already there.

3. Make sure table/index statistics are updated regularly.

SQL to identify the query which takes long time

Step1 :  Run the first query ,  this will list all the programs that currently running in Application. Take the SID and use it in the second query.

Oracle Performance Tuning related Queries

• SQL to identify the query which takes long time
• Sequence of SQL query processed