Back to Papers
 
 

Introduction

Definitions

Basic Usage

Logic-dependent Aggregation

An Interlude

Logic-dependent Aggregation (contd)

Pivot Tables and Multi-part Logic

Beyond Equality

Conclusion

Additional Resources

Please note this page has moved to http://oracledoug.com/case5.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

The execution plan generated by Oracle for example 2b is as follows 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=879 Card=18 Bytes=1152)

   1    0   SORT (ORDER BY) (Cost=878 Card=18 Bytes=1152)
   2    1     UNION-ALL
   3    2       SORT (GROUP BY) (Cost=433 Card=3 Bytes=192)
   4    3         HASH JOIN (Cost=429 Card=45967 Bytes=2941888)
   5    4           TABLE ACCESS (FULL) OF 'TIMES' (TABLE) (Cost=15 Card=365 Bytes=4380)
   6    4           HASH JOIN (Cost=412 Card=183769 Bytes=9555988)
   7    6             TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS' (TABLE) (Cost=3 Card=14                                                                         Bytes=490)
   8    7               INDEX (RANGE SCAN) OF 'PRODUCTS_PROD_CAT_IX' (INDEX) (Cost=1 Card=14)    9    6             PARTITION RANGE (ITERATOR) (Cost=400 Card=918843 Bytes=15620331)
  10    9               TABLE ACCESS (FULL) OF 'SALES' (TABLE) (Cost=400 Card=918843                                                                         Bytes=15620331)
  11    2       SORT (GROUP BY) (Cost=444 Card=15 Bytes=960)
  12   11         HASH JOIN (Cost=430 Card=183869 Bytes=11767616)
  13   12           TABLE ACCESS (FULL) OF 'PRODUCTS' (TABLE) (Cost=3 Card=58 Bytes=2030)
  14   12           HASH JOIN (Cost=424 Card=229837 Bytes=6665273)
  15   14             TABLE ACCESS (FULL) OF 'TIMES' (TABLE) (Cost=15 Card=365 Bytes=4380)
  16   14             PARTITION RANGE (ITERATOR) (Cost=400 Card=918843 Bytes=15620331)
  17   16               TABLE ACCESS (FULL) OF 'SALES' (TABLE) (Cost=400 Card=918843                                                                         Bytes=15620331)
Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
       1173  consistent gets
          0  physical reads
          0  redo size
       1306  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         22  rows processed

As this is the first execution plan we’ve come across, it’s worth a brief interlude to examine it in a little more detail.  I’ll use the step numbers in the first column for reference. 

The first important point is that the cost based optimizer chose different plans for the two different result sets which are UNIONed. That’s because, although they look very similar, they are interested in different volumes of data, so different access paths are appropriate.

First Query Block (for Photo sales)

• Steps 8 and 7 retrieve the rows for Photo products from PRODUCTS, using an index range scan. PRODUCTS_PROD_CAT_IX is a non-unique index on the PROD_CATEGORY column. Because Photo products are a small subset of PRODUCTS, Oracle has decided that an indexed retrieval is most efficient.
• Steps 10, 9 and 6 retrieve the related rows from the partitioned SALES table using a Hash Join against a full table scan of SALES
• Steps 5 and 4 retrieve all the related rows from the TIMES table using a Hash Join
• Step 3 groups the resulting set of data from PRODUCTS, SALES and TIMES for Photo products. 

Second Query Block (for non-Photo sales)

• Step 15 retrieves all of the rows for calendar year 2001 from the TIMES table using a full table scan.
• Steps 14, 16 and 17 retrieve all of the related rows from the partitioned SALES table using a full table scan and a Hash Join.
• Steps 13 and 12 retrieve all of the rows from the PRODUCTS table (eliminating Photo products) and then use a Hash Join to join the results to the last rowset. Note that, because we need to retrieve nearly all of the rows from the products table, it’s more efficient for Oracle to use a full table scan this time.
• Step 11 groups the resulting set of data from PRODUCTS, SALES and TIMES for Photo products.

• Step 2 performs a UNION ALL operation on the results from d) and h) above
• Step 1 performs the final sort of the aggregated results, so that they’re ORDERed BY PROD_CATEGORY then PROD_SUBCATEGORY

I like the autotrace facility because it allows me to run the query, see the results, the execution plan and some basic resource usage statistics. However when it comes to reading the execution plan, a nicer facility is probably the DBMS_XPLAN package, so I suggest you read the documentation and try that too.

Interlude over - let’s get back to tuning the query.

Previous