complex unnion all

cass cheng

complex unnion all
Dear Listers,

I have a sql query where i have three set of results which is getting
unioned. The entire SQL includes 8 table and each set there are join
among tables. Each set is then unionedto produce the final result. But
in each set there is a common table which is driving the query in set.
But the query is reasonable time. But as there is one table which is
common in all the three sets of result then can it be made that there
should be one pass to the common table.

Can any ne suggest how come i would reduce only one pass to the common
table TABLEA with the same resultset i am getting right now. I feel if
one pass is there then i can make the query to run more faster. the query
is below.

SELECT A.COL1,A.COL2,B.COL3,B.COL4,B.COL5,C.COL6,C.COL7
FROM TABLEA JOIN TABLEB ON A.COL1=B.COL1 AND B.COL2 IN ('XXX','YYY')
JOIN TABLEC
ON B.COL3=C.COL3
JOIN TABLED
ON B.COL5=D.COL5

WHERE A.COL1 IN ('XXXXXX','YYYYY','ZZZZZ')
UNION ALL
SELECT A.COL1,A.COL2,E.COL3,F.COL4,E.COL5,F.COL6,G.COL7
FROM TABLEA JOIN TABLEE
ON A.COL3=E.COL3
JOIN TABLEF
ON E.COL6=F.COL6
JOIN TABLEG
ON F.COL5=G.COL5
WHERE A.COL1 IN ('XXXXXX','YYYYY','ZZZZZ')
UNION ALL
SELECT A.COL1,H.COL2,H.COL3,H.COL4,H.COL5,H.COL6,H.COL7
FROM TABLEA JOIN TABLEH
ON A.COL8=H.COL8
WHERE A.COL1 IN ('XXXXXX','YYYYY','ZZZZZ');

Thanking you in advance

Cass

--
___________________________________________________
Play 100s of games for FREE! http://games.mail.com/


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: complex unnion all
(in response to cass cheng)
There are techniques you can use if you can guarantee that a
given row from TABLEA will result in not more than one row in the
final result set - that is either in will join to either a TABLEB row or
a TABLEE row or a TABLEH row but not more than one of these. If
you cannot guarantee that, then the cost of handling the resulting
duplicates will outweigh the saving from process TABLEA once.


SELECT A.COL1,
CASE WHEN B.COL3 IS NULL AND E.COL3 IS NULL THEN
H.COL2
ELSE A.COL2 END AS COL2,
CASE WHEN B.COL3 IS NULL AND E.COL3 IS NULL THEN
H.COL3
WHEN B.COL3 IS NULL THEN E.COL3
ELSE B.COL3 END AS COL3,
...
FROM TABLEA A
LEFT OUTER JOIN TABLEB B ON A.COL1=B.COL1 ....
LEFT OUTER JOIN TABLEE E ON ON A.COL3=E.COL3 ...
LEFT OUTER JOIN TABLEH H ON A.COL8=H.COL8 ...
WHERE COL3 IS NOT NULL
AND A.COL1 IN ('XXXXXX','YYYYY','ZZZZZ')

Whether this actually more efficient is something only testing will
show.

James Campbell


On 2 Dec 2005 at 19:22, cass cheng wrote:

>
> Dear Listers,
> I have a sql query where i have three set of results which is getting unioned. The entire SQL
> includes 8 table and each set there are join among tables. Each set is then unionedto produce
> the final result. But in each set there is a common table which is driving the query in set. But the
> query is reasonable time. But as there is one table which is common in all the three sets of result
> then can it be made that there should be one pass to the common table.
>
> Can any ne suggest how come i would reduce only one pass to the common table TABLEA with
> the same resultset i am getting right now. I feel if one pass is there then i can make the query to
> run more faster. the query is below.
> SELECT A.COL1,A.COL2,B.COL3,B.COL4,B.COL5,C.COL6,C.COL7
> FROM TABLEA JOIN TABLEB ON A.COL1=B.COL1 AND B.COL2 IN ('XXX','YYY')
> JOIN TABLEC
> ON B.COL3=C.COL3
> JOIN TABLED
> ON B.COL5=D.COL5
> WHERE A.COL1 IN ('XXXXXX','YYYYY','ZZZZZ')
> UNION ALL
> SELECT A.COL1,A.COL2,E.COL3,F.COL4,E.COL5,F.COL6,G.COL7
> FROM TABLEA JOIN TABLEE
> ON A.COL3=E.COL3
> JOIN TABLEF
> ON E.COL6=F.COL6
> JOIN TABLEG
> ON F.COL5=G.COL5
> WHERE A.COL1 IN ('XXXXXX','YYYYY','ZZZZZ')
> UNION ALL
> SELECT A.COL1,H.COL2,H.COL3,H.COL4,H.COL5,H.COL6,H.COL7
> FROM TABLEA JOIN TABLEH
> ON A.COL8=H.COL8
> WHERE A.COL1 IN ('XXXXXX','YYYYY','ZZZZZ');
> Thanking you in advance
> Cass
>
>
> --
> ___________________________________________________
> Play 100s of games for FREE! http://games.mail.com/
> --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list.
> To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-
> l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
> [login to unmask email] Find out the latest on IDUG conferences at
> http://conferences.idug.org/index.cfm



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

cass cheng

Re: complex unnion all
(in response to James Campbell)
Thanks James,

It is one one row and no duplicacy so, I am trying the way you
suggested. I will come back once I am done with that.

Bye the way can you advice me what is the equivalent of MODULUS scalar
function in db2. Like I have predicates

where MOD(COL1,5) = 2 .

I am sure the MOD function is a scalar function and even the access path
show the index access with hybrid join , but it would be retrived in
stage 2. is there a way this modulus fucntion can be substitued.

Thanking you in advance.

Cass



----- Original Message -----
From: "James Campbell"
To: [login to unmask email]
Subject: Re: [DB2-L] complex unnion all
Date: Sat, 3 Dec 2005 15:49:48 -0800

There are techniques you can use if you can guarantee that a given
row from TABLEA will result in not more than one row in the final
result set - that is either in will join to either a TABLEB row or a
TABLEE row or a TABLEH row but not more than one of these. If you
cannot guarantee that, then the cost of handling the resulting
duplicates will outweigh the saving from process TABLEA once.

SELECT A.COL1,CASE WHEN B.COL3 IS NULL AND E.COL3 IS NULL THEN H.COL2
ELSE A.COL2 END AS COL2,CASE WHEN B.COL3 IS NULL AND E.COL3 IS NULL
THEN H.COL3 WHEN B.COL3 IS NULL THEN E.COL3 ELSE B.COL3 END AS COL3,...FROM
TABLEA ALEFT OUTER JOIN TABLEB B ON A.COL1=B.COL1 ....LEFT OUTER
JOIN TABLEE E ON ON A.COL3=E.COL3 ...LEFT OUTER JOIN TABLEH H ON
A.COL8=H.COL8 ...
WHERE COL3 IS NOT NULLAND A.COL1 IN ('XXXXXX','YYYYY','ZZZZZ')
Whether this actually more efficient is something only testing will
show.
James Campbell

--
___________________________________________________
Play 100s of games for FREE! http://games.mail.com/


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bernd Oppolzer

Re: complex unnion all
(in response to cass cheng)
I guess, this will always be stage 2, even if you substitute the MOD
function by an arithmetic expression. There is only a solution, if the
column has a known set of values, so you could list the MOD(5) = 2 values
in an IN list, like in

where col1 in (2, 7, 12, 17);

Kind regards

Bernd


Am Sonntag, 4. Dezember 2005 10:59 schrieben Sie:
> Thanks James,
>
> It is one one row and no duplicacy so, I am trying the way you
> suggested. I will come back once I am done with that.
>
> Bye the way can you advice me what is the equivalent of MODULUS scalar
> function in db2. Like I have predicates
>
> where MOD(COL1,5) = 2 .
>
> I am sure the MOD function is a scalar function and even the access path
> show the index access with hybrid join , but it would be retrived in
> stage 2. is there a way this modulus fucntion can be substitued.
>
> Thanking you in advance.
>
> Cass
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm