DB2 join challenge

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

redeck - 20 Dec 2005 14:04 GMT

I have table A with 2 columns: PK [integer] and Txt [varchar(128)],
size - 1000 rows. Also, I have table B with 100000 rows and 100 integer
columns, each one is a foreign key to A's PK. I need to create a view
in which every cell of B is replaced by the Txt from A according to the
PKs as in the example below. I assume I need to perform a join, but the
SQL I used writes a huge (several GB) temporary tablespace and thus it
is very slow. Any suggestions?

B:
X Y Z ...
----------------
1 3 5
3 2 1

A:
PK Txt
-----------
1 a
2 b
3 c
5 d

Resulting view:
X Y Z ...
----------------
a c d
c b a

Reply to this Message

Knut Stolze - 20 Dec 2005 15:14 GMT

> I have table A with 2 columns: PK [integer] and Txt [varchar(128)],
> size - 1000 rows. Also, I have table B with 100000 rows and 100 integer
[quoted text clipped - 23 lines]
> a c d
> c b a

Well, what have you tried already?

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

redeck - 21 Dec 2005 07:00 GMT

CREATE VIEW VIEW_1 AS SELECT T1.TXT X, T2.TXT Y, T3.TXT Z FROM B LEFT
JOIN A AS T1 ON B.X = T1.PK LEFT JOIN A AS T2 ON B.Y = T2.PK LEFT JOIN
A AS T3 ON B.Z = T3.PK

Reply to this Message

Knut Stolze - 21 Dec 2005 07:58 GMT

> CREATE VIEW VIEW_1 AS SELECT T1.TXT X, T2.TXT Y, T3.TXT Z FROM B LEFT
> JOIN A AS T1 ON B.X = T1.PK LEFT JOIN A AS T2 ON B.Y = T2.PK LEFT JOIN
> A AS T3 ON B.Z = T3.PK

Why are you using a left outer join here? If I got this right, you'll not
have the case that there are any rows in table B that are not in A, right?
So you should try the following first:

SELECT t1.txt, t2.txt, t3.txt
FROM b JOIN a AS t1 ON ( b.x = t1.pk )
JOIN a AS t2 ON ( b.y = t2.pk )
JOIN a AS t3 ON ( b.z = t3.pk )

I would prefer the following way of writing query as it makes things more
explicit. However, I would not be surprised if that gives exactly the same
plan as the query above.

SELECT ( SELECT txt FROM a WHERE a.pk = b.x ),
( SELECT txt FROM a WHERE a.pk = b.y ),
( SELECT txt FROM a WHERE a.pk = b.z ),
FROM b

If removing the outer join is not leading to the desired results, you should
verify that you have the proper indexes defined on both tables. One index
on A(PK) is needed anyways for the primary key. Three indexes on B(X),
B(Y), and B(Z) might be helpful. Additionally you could try to use an
index A(PK) and include the TXT column. That way, it might be possible to
answer the query completely with index access only.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

mittalashish@gmail.com - 21 Dec 2005 11:11 GMT

Assuming that the table B has a primary key column, something like this
might work:

select b.pk, max(x) x, max(y) y, max(z) z ....
from (
select b.pk, case when b.x=a.pk then a.txt end x,
case when b.y=a.pk then a.txt end y,
case when b.z=a.pk then a.txt end....
from b,a where
b.x=a.pk or b.y=a.pk or b.z=a.pk....) etc.

Reply to this Message

redeck - 22 Dec 2005 16:05 GMT

Without the LEFT modifier I am getting the SQL0101N error: The
statement is too long or too complex. This is very strange because I
have STMTHEAP = 32768 x 4KB pages = 128 MB.

Reply to this Message

Brian Tkatch - 22 Dec 2005 16:29 GMT

This is just a wild guess.

Is it possible to split the query into smaller parts without LEFT, to
avoid the to complex error, and then use another view to UNION ALL them
together.

B.

Reply to this Message