Re: Optimization of Partitioned Outer Joins
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <Christian.Antognini@xxxxxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Mon, 3 Jan 2005 02:02:17 +0100
Hi Christian,
> i.e. to me it seams that the partitioned outer join prevents the CBO to
use the join predicate as access predicate!
exact and in my opinion correct. Otherwise you can get a different result of
the query as illustrated in a very simplified version of your query below.
The critical point is see definition of query_partition_clause
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_10002.htm#i2197413
<quote>
The result of a partitioned outer join is a UNION of the outer joins of each
of the partitions in the partitioned result set and the table on the other
side of the join.
</quote>
My interpretation is, that the partitions are build first, followed by the
join.
Note, that in my example the required channel_id = 9 doesn't exist in the
constrained time (time_id =1) but is returned in the first query. The second
query, that constraint the sales table on time_id = 1 return no data.
I thing this is a very interesting point which makes partitioned outer join
on big fact tables more dangerous and I agree that the "native" explanation:
first constraint the fact table than build the partitions, would be more
straightforward.
It will be interesting to see the ANSI definition of partitioned outer join.
Another minor point is the where clause (WHERE channel_id =9). In my
opinion, if the channel_id is known (and only one), there is no need to
perform partitioned join.
regards
Jaromir D.B. Nemec
-- example ---
SQL> create table sales
2 (channel_id number,
3 time_id number,
4 amount_sold number);
Table created.
SQL> --
SQL> create table times
2 (time_id number);
Table created.
SQL> ---
SQL> insert into times
2 select rownum time_id from dba_objects where rownum < 3;
2 rows created.
SQL> --
SQL> insert into sales values (1,1,1);
1 row created.
SQL> insert into sales values (9,2,1);
1 row created.
SQL> commit;
Commit complete.
SQL> --
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
9 ;
CHANNEL_ID TIME_ID AMOUNT_SOLD
---------- ---------- -----------
9 1
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 on (s.time_id = s.time_id)
8 WHERE channel_id = 9 and
9 s.time_id = 1
10 ;
no rows selected
SQL>
----- Original Message -----
From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, January 03, 2005 12:15 AM
Subject: RE: Optimization of Partitioned Outer Joins
--
//www.freelists.org/webpage/oracle-l
Other related posts:
