How A Query Executes in Oracle

sandeepgupta_18

Here are the rules Oracle follows to execute each query, and the order in which execution takes place:

1. Choose rows based on the WHERE clause.

2. Group those rows together based on the GROUP BY clause.

3. Calculate the results of the group functions for each group.

4. Choose and eliminate groups based on the HAVING clause.

5. Order the groups based on the results of the group functions in the ORDER BY clause. The ORDER BY clause must use either a group function or a column specified in the GROUP BY clause.

The order of execution is important because it has a direct impact on the performance of your queries. In general, the more records that can be eliminated via the WHERE clause, the faster the query will execute. This performance benefit is because of the reduction in the number of rows that must be processed during the GROUP BY operation.

If a query is written to use a HAVING clause to eliminate groups, you should check to see if the HAVING condition can be rewritten as a WHERE clause. In many cases, this rewrite won’t be possible. It is usually only available when the HAVING clause is used to eliminate groups based on the grouping columns.

For example, suppose you have this query:

Select designation, count(*), avg(salary)

From employee

Where salary>1000

Group by designation

having designation like ‘%S%’

order by count(*) desc;

The order of execution would be as follows:

1. Eliminate rows based on

Where salary >1000

2. Group the remaining rows based on

Group by designation

3. For each CATEGORY_NAME, calculate the

Count(*)

4. Eliminate groups based on

Having designation like ‘%S%’

5. Order the remaining groups.

This query will run faster if the groups eliminated in Step 4 can be eliminated as rows in Step 1. If they are eliminated at Step 1, fewer rows will be grouped (Step 2), fewer calculations will be performed (Step 3), and no groups will be eliminated (Step 4). Thus, each of these steps in the execution will run faster.

Frank Kulash

Hi,

Here are the rules Oracle follows to execute each query, and the order in which execution takes place:

1. Choose rows based on the WHERE clause.

2. Group those rows together based on the GROUP BY clause.

3. Calculate the results of the group functions for each group.

4. Choose and eliminate groups based on the HAVING clause.

It can be helpful to imagine that Oracle is performing these steps in that order. Whether it actually does or not is another story, and may vary from one version to another. For example, it doesn't wait until all the rows are chosen before it starts grouping them and computing the aggregate functions.

5. Order the groups based on the results of the group functions in the ORDER BY clause. The ORDER BY clause must use either a group function or a column specified in the GROUP BY clause.

That's assuming you're using a GROUP BY clause and/or aggregate functions. Expressions based on the things you mentioned (as well as constants) are allowed. (SYSDATE is considered a constant for this purpose.) For example:

GROUP BY  str
ORDER BY  SUBSTR (str, 5, 3)

is allowed, even though SUBSTR is not an aggregate function, and SUBSTR (str, 5, 3) is not specified in the GROUP BY clause.