Home
Page
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 ;-)
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)
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.
UNION and ORDER BY
-
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
|