Re: Optimization of Partitioned Outer Joins

  • From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
  • To: <Christian.Antognini@xxxxxxxxxxxx>
  • Date: Mon, 3 Jan 2005 14:26:58 +0100

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 --


  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;


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

         9          1

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


  2  FROM (select * from sales s

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

  4                             ---<< better performance, but different

  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


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).


----- 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


Other related posts: