Re: Optimization of Partitioned Outer Joins

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Mon, 3 Jan 2005 14:26:58 +0100
Message-ID: <010f01c4f197$e7d3f540$3c02a8c0@JARAWIN>

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_at_trivadis.com> To: "jaromir nemec" <jaromir_at_db-nemec.com> Cc: <oracle-l_at_freelists.org> Sent: Monday, January 03, 2005 11:16 AM Subject: RE: Optimization of Partitioned Outer Joins

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

Received on Mon Jan 03 2005 - 07:22:34 CST