Re: Optimization of Partitioned Outer Joins

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_at_trivadis.com> To: <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> Sent: Monday, January 03, 2005 12:15 AM Subject: RE: Optimization of Partitioned Outer Joins

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