RE: Optimization of Partitioned Outer Joins

  • From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
  • To: <Christian.Antognini@xxxxxxxxxxxx>, "'jaromir nemec'" <jaromir@xxxxxxxxxxxx>
  • Date: Mon, 3 Jan 2005 17:05:44 +0100

 
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> ::=
    <left paren> <partitioned join column reference>
    [ { <comma> <partitioned join column reference> }... ]
    <right paren>

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:
      1) If TVA is empty, then let NA be 0 (zero).
      2) 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.
c) Case:
   i) If the second operand specifies <partitioned join table>, then Case:
      1) If TVB is empty, then let NB be 0 (zero).
      2) 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.
d) 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.
e) 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.
f) 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.
g) 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.
h) 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:
   i)  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.
i) 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:
   i)  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.
j) 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.
k) 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.
l) 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Christian Antognini
Sent: Monday, January 03, 2005 11:17
To: jaromir nemec
Cc: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l



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

Other related posts: