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 --
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
