An Interlude

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)

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)

Second Query Block (for non-Photo sales)


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.


Improving SQL efficiency using CASE