Re: Optimization of Partitioned Outer Joins

Hi Chris,



> Mhmm... I don't know what you are trying to show with your query...

> I guess that the following two queries show what do you want to show.



>   9  AND t.time_id = 1;

>   9  AND s.time_id = 1;



No, this was not the point.



Sorry, for not being exact enough. I simply meant, if the sales table would
be restricted with the same constraint as the times table, you'll get
different result (based on possible less partitions of channel_id). So this
couldn't be done simple by optimiser behind the scenes.



I try to reformulate this:



-- original statement --

SQL> SELECT *

  2  FROM sales s PARTITION BY (channel_id)

  3               RIGHT OUTER JOIN (

  4                 SELECT time_id

  5                 FROM times t

  6                 WHERE t.time_id = 1

  7               ) t USING (time_id)

  8  WHERE channel_id = 9;



CHANNEL_ID    TIME_ID AMOUNT_SOLD

---------- ---------- -----------

         9          1

--- optimised statement. If the optimizer would apply the additional access
predicate, the result  of the query would be different ---

SQL> SELECT *

  2  FROM (select * from sales s

  3        where s.time_id = 1) ---<< additional access predicate

  4                             ---<< better performance, but different
result

  5               PARTITION BY (channel_id)

  6               RIGHT OUTER JOIN (

  7                 SELECT time_id

  8                 FROM times t

  9                 WHERE t.time_id = 1

 10               ) t USING (time_id)

 11  WHERE channel_id = 9;



no rows selected



SQL>



In general, I understand the partition outer join more as a mechanism to
fill the gaps in a big table with the additional values from a small table,
then vice versa.

I.e. in this context I would use it more for sales pictures per channel
(inclusive  channels without movement) than for sales pictures per channel
(inclusive  channels not defined in the channel table).





Jaromir

----- Original Message ----- 
From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
To: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, January 03, 2005 11:16 AM
Subject: RE: Optimization of Partitioned Outer Joins



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

Other related posts: