Here are the rules Oracle uses to execute each query, and the order in which execution takes place:
- Choose rows based on the WHERE clause.
- Group those rows together based on the GROUP BY clause.
- Calculate the results of the group functions for each group.
- Choose and eliminate groups based on the HAVING clause.
- 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:
The order of execution would be as follows:
1. Eliminate rows based on
2. Group the remaining rows based on
3
3. For each CATEGORY_NAME, calculate the
4. Eliminate groups based on
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.
Because the HAVING condition in this example is not based on a calculated column, it is easily changed into a WHERE condition:
In the modified version, fewer rows are grouped, resulting in a performance savings. As the number of rows in your tables increases, the performance savings from eliminating rows early in the process can grow dramatically.
This example may seem trivial because the table has few rows in it. But even such a small query can have a significant impact in a production application. There are many examples of production applications whose performance is severely impacted by high volumes of executions of seemingly small queries. When those small queries are executed thousands or millions of times per day, they become the most resource-intensive queries in the database. When planning the SQL access paths for your application, tune even the small queries.
Leave a Reply