RE: Optimization of Partitioned Outer Joins

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Mon, 3 Jan 2005 17:05:44 +0100
Message-Id: <20050103165450.B392FDBBE@node42.naturaljoin.nl>

The ANSI/ISO standard has the following to say about partitioned outer joins:
(if you are not interested, hit the delete key now :-)

================ quote from 7.7: <joined table> =========================

<partitioned join table> ::=

<table factor> PARTITION BY
<partitioned join column reference list>

<partitioned join column reference list> ::=

Syntax rule 7:
If a <partitioned join table> PJT is specified, then: a) The <qualified join> or <natural join> shall specify an <outer join type>.
b) Each <partitioned join column reference> shall uniquely reference a column of the table referenced by

the <table primary> simply contained in PJT. Such a column is called a join partitioning column.
c) If the first operand of the <qualified join> or <natural join> is a
<partitioned join table>, then the <outer

join type> shall be RIGHT or FULL.
d) If the second operand of the <qualified join> or <natural join> is a
<partitioned join table>, then the

<outer join type> shall be LEFT or FULL, and TRB shall not contain a
<lateral derived table> containing

an outer reference that references TRA.

General Rule4:
If RIGHT or FULL is specified or if LEFT is specified and the second operand specifies <partitioned join
table>, then:
a) Let TVB be the result of evaluating TRB.

NOTE 138 - It follows from the Syntax Rules that TRB does not contain a
<lateral derived table>

containing an outer reference that references TRA. This ensures that it is possible to evaluate TRB in isolation. b) Case:

i) If the first operand specifies <partitioned join table>, then Case:

If TVA is empty, then let NA be 0 (zero).
Otherwise, TVA is partitioned into the minimum numbers of partitions such that for each join partitioning column JPC of each partition, no two values of JPC are distinct. If the declared type of a join partitioning column is a user-defined type and the comparison of that column results in Unknown for two rows of TVA, then the assignment of those rows to partitions is implementation-dependent. Let NA be the number of partitions. Let GA1, ... , GANA be an enumeration of the partitions. ii) Otherwise, let NA be 1 (one), and let GA1 be TVA.
Case:
If the second operand specifies <partitioned join table>, then Case:
If TVB is empty, then let NB be 0 (zero).
Otherwise, TVB is partitioned into the minimum numbers of partitions such that for each join partitioning column JPC of each partition, no two values of the join partitioning column are distinct. If the declared type of a join partitioning column is a user-defined type and the comparison of that column results in Unknown for two rows of TVB, then the assignment of those rows to partitions is implementation-dependent. Let NB be the number of partitions. Let GB1, ... , GBNB be an enumeration of the partitions. ii) Otherwise, let NB be 1 (one), and let GB1 be TVB.
For each i between 1 (one) and NA, and for each j between 1 (one) and NB, let PAi, j be the collection of rows RA of GAi for which there exists a row RB in GBj such that the concatenation of RA and RB is in TR.
For each i between 1 (one) and NA, and for each j between 1 (one) and NB, let PBi, j be the collection of rows RB of GBi for which there exists a row RA in GAj such that the concatenation of RA and RB is in TR.
For each i between 1 (one) and NA, and for each j between 1 (one) and NB, let UAi, j be the collection of rows of GAi that are not in PAi, j.
For each i between 1 (one) and NA, and for each j between 1 (one) and NB, let UBi, j be the collection of rows of GBi that are not in PBi, j.
For each i between 1 (one) and NA, and for each j between 1 (one) and NB, let XAi, j be UAi, j extended on the right with DB columns, with declared types and values determined as follows. For each k between 1 (one) and DB, the declared type of the (DA + k)-th column is the declared type of the k-th column of TVB, and the value is Case:
If the k-th column of TVB is a join partitioning column, then the common value of the k-th column of GBj. ii) Otherwise, the null value.
For each i between 1 (one) and NA, and for each j between 1 (one) and NB, let XBi, j be UBi, j extended on the left with DA columns, with declared types and values determined as follows. For each k between 1 (one) and DA, the declared type of the k-th column is the declared type of the k-th column of TVA, and the value is Case:
If the k-th column of TVA is a join partitioning column, then the common value of the k-th column of GAi. ii) Otherwise, the null value.
Let XA be the collection of all rows in XAi, j for all i between 1 (one) and NA and all j between 1 (one) and NB. NOTE 139 - If NA is 0 (zero), then XA is empty.
Let XB be the collection of all rows in XBi, j, for all i between 1 (one) and NA and all j between 1 (one) and NB. NOTE 140 - If NB is 0 (zero), then XB is empty.
Let XNB be an effective distinct name for XB.

Conformance Rule 5:
Without Feature F403, "Partitioned join tables", conforming SQL language shall not contain <partitioned
join table>.

Lex.

Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christian Antognini
Sent: Monday, January 03, 2005 11:17
To: jaromir nemec
Cc: oracle-l_at_freelists.org
Subject: RE: Optimization of Partitioned Outer Joins

Hi Jaromir

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

Mhmm... I don't know what you are trying to show with your query... but = at least the ON clause is wrong... I guess that the following two = queries show what do you want to show. (notice that I have no problem = with the following results!)

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 =3D 1
  7               ) t ON t.time_id =3D s.time_id

8 WHERE channel_id =3D 9
9 AND t.time_id =3D 1;

CHANNEL_ID TIME_ID AMOUNT_SOLD TIME_ID ---------- ---------- ----------- ----------

         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 =3D 1
  7               ) t ON t.time_id =3D s.time_id

8 WHERE channel_id =3D 9
9 AND s.time_id =3D 1;

no rows selected

>My interpretation is, that the partitions are build first, followed by >=
the
>join.

I agree, but I don't see why during the join the condition cannot be = used as access predicate.

>Note, that in my example the required channel_id =3D 9 doesn't exist in >=
the
>constrained time (time_id =3D1) but is returned in the first query. The >=
second
>query, that constraint the sales table on time_id =3D 1 return no data.

See my previous remark...

>It will be interesting to see the ANSI definition of partitioned outer >=
join.

AFAIK it's not part to SQL 2003, therefore we have to wait some time.

>Another minor point is the where clause (WHERE channel_id =3D9). In my 
>opinion, if the channel_id is known (and only one), there is no need to 
>perform partitioned join.

This is a simplified query. In the original one the restriction was on = another column of the table CHANNELS, i.e. not on the PK.

Chris

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



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

Received on Mon Jan 03 2005 - 10:05:24 CST