| Home
Page
Logic-dependent Aggregation (contd) |
Please note this page has moved to http://oracledoug.com/case6.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-) Logic-dependent Aggregation (contd)The only reason that we require two scans of SALES in example 2b is to return all the non-Photo products and their amount_sold in one data set, using SUM(amount_sold); and to return another data set containing the Photo products, using SUM(amount_sold) * 1.1 to calculate the total amount_sold. The two sets are then UNIONed.
We can optimise this query by retrieving all of the amount_sold values in one scan of the SALES table and then using CASE to selectively apply a calculation to the results for the Photo products in the SELECT list, as shown in example 2c.
Example 2c Although the results are identical and the two statements are functionally equivalent, it is clear from the execution plan that this will require only one full scan of the sales table, which represents a useful improvement. Example 2c requires half the number of consistent gets and less than half the number of sorts that example 2b does. However, you’ll probably notice that if you run this in a single user test environment that, because we’re operating on fairly small volumes of data, the time to complete the requests and return the data is very similar – around 1 second in my tests. Which is why generating the execution plans and resource usage statistics is important. If you had many users running this report against larger data sets, the difference would become more noticeable. So let's look at what we've changed. We'll leave the SELECT clause until last (because that is where the most significant changes are) and exclude the more straightforward parts of the statement first. We still need to select FROM the same three tables and to GROUP BY product_category and product_subcategory, so no change in those two parts of the statement. We know we're interested in all products so let's eliminate the product_category check from the two different WHERE clauses in example 2b which leaves us with two identical WHERE clauses which facilitate the joins between the sales, times and product tables and limit the data to the calendar year 2001. Now that the two WHERE clauses are identical they return the same rows so we can reduce everything to one data set, with no need for the UNION any more. In fact, the query is starting to look like example 2a. FROM sales s, times t, products p WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id AND t.calendar_year = 2001 GROUP BY p.prod_category, p.prod_subcategory ORDER BY p.prod_category, p.prod_subcategory; This leaves us with our new SELECT clause to look at. The first two grouping columns remain the same - product_category and product_subcategory from the products table. The third column specification contains some of the logic which we've moved from the WHERE clauses of the UNION. It uses the SUM() function to generate a total amount_sold for all the products in the product_category and product_subcategory but uses different values for amount_sold, depending on whether the category is ‘Photo’ or not. So here is a high-level procedural view of how example 2c works.
|