Re: Optimization of Partitioned Outer Joins

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 3 Jan 2005 09:03:48 -0000
Message-ID: <01b601c4f173$26ec2600$6702a8c0@Primary>

I think the answer is 'generic use'.

It would be a little unusual to access a very large fact table from just one dimension table.

Imagine joining 4 dimensions to the fact table - and then wanting to produce a
report which actually did what the
partitioned outer join was designed to
do - i.e. show the gaps with zeros.

You either have to construct the
query very carefully (much as you
have done with your single dimension)
or you have to make every join in
sight an outer join so that the joins
from the other three dimensions do
not eliminate the rows generated by
the partition outer join.

It may be that your example simply falls into a special degenerate case that could be addressed with some custom code (in
the optimizer), but that's the sort of thing that tends to get addressed a few minor releases down the line.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004

Original Message ----- From: "Christian Antognini" <Christian.Antognini_at_trivadis.com> 3) the modified query (TIMES is joined once more to enable an index access)

SELECT t.fiscal_month_number,

nvl(sum(s.amount_sold),0) amount_sold FROM sales s JOIN (

               SELECT time_id
               FROM times
               WHERE fiscal_year = 1998
               AND fiscal_quarter_number = 2
             ) t1 USING (time_id)
             PARTITION BY (channel_id)
             RIGHT OUTER JOIN (
               SELECT time_id, fiscal_month_number
               FROM times
               WHERE fiscal_year = 1998
               AND fiscal_quarter_number = 2
             ) t USING (time_id)

WHERE channel_id = 9
GROUP BY t.fiscal_month_number;

| Id | Operation | Name |

| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | VIEW | |
| 3 | MERGE JOIN PARTITION OUTER | |
| 4 | SORT JOIN | |
| 5 | VIEW | |

|*  6 |       TABLE ACCESS FULL                | TIMES             |
|*  7 |     SORT PARTITION JOIN                |                   |


|   8 |      VIEW                              |                   |

|   9 |       TABLE ACCESS BY LOCAL INDEX ROWID| SALES             |

|  10 |        NESTED LOOPS                    |                   |

|* 11 |         TABLE ACCESS FULL              | TIMES             |


|  12 |         PARTITION RANGE ITERATOR       |                   |

|  13 |          BITMAP CONVERSION TO ROWIDS   |                   |

|  14 |           BITMAP AND                   |                   |

|* 15 |            BITMAP INDEX SINGLE VALUE   | SALES_TIME_BIX    |
|* 16 |            BITMAP INDEX SINGLE VALUE   | SALES_CHANNEL_BIX |
--------------------------------------------------------------------

Predicate Information (identified by operation id):

   6 - filter("FISCAL_YEAR"=1998 AND "FISCAL_QUARTER_NUMBER"=2)
   7 - access("T"."TIME_ID"="from$_subquery$_004"."TIME_ID")
       filter("T"."TIME_ID"="from$_subquery$_004"."TIME_ID")
  11 - filter("FISCAL_YEAR"=1998 AND "FISCAL_QUARTER_NUMBER"=2)
  15 - access("S"."TIME_ID"="TIME_ID")
  16 - access("S"."CHANNEL_ID"=9)

This query is much faster (ca. factor 5) and does much less LIO (ca. factor 3.5). Of course in this case there is no big difference.... but in a real star schema it will be a real problem...

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 03 2005 - 02:59:04 CST