UNION and JOIN are two very different concepts. Do the query execute quickly if you change from FULL JOIN to simply JOIN (i.e. from an outer join to an inner join)? I hardly ever use FULL JOIN and probably cannot be of much help, but I thought that if there are a combination of C1, C2, C3 that occurs 10000 times in both D1 and D2, then this combination will result in 100 million rows in the (intermediate) result set with both FULL JOIN and JOIN, whereas it would only be 20000 in a UNION. If JOIN is quick and FULL JOIN never returns, well, show us the PLANs for both statements and maybe someone will be able to understand a bit more.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of gusta1308
Sent: 13. oktober 2009 02:41
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Firebird 2.5 Beta 2 65267 Full join

I do trying run follow query:

SELECT
COALESCE(D1.C1, D2.C1) C1,
COALESCE(D1.C2, D2.C2) C2,
COALESCE(D1.C3, D2.C3) C3,
COALESCE(SUM(D1.D1), 0) T1,
COALESCE(SUM(D2.D2), 0) T2
FROM TD_DATA1 D1
FULL JOIN TD_DATA2 D2
ON D2.C1 = D1.C1
AND D2.C2 = D1.C2
AND D2.C3 = D1.C3
GROUP BY COALESCE(D1.C1, D2.C1),
COALESCE(D1.C2, D2.C2),
COALESCE(D1.C3, D2.C3)

or

SELECT
D1.C1, D2.C1,
D1.C2, D2.C2,
D1.C3, D2.C3,
COALESCE(SUM(D1.D1), 0) T1,
COALESCE(SUM(D2.D2), 0) T2
FROM TD_DATA1 D1
FULL JOIN TD_DATA2 D2
ON D2.C1 = D1.C1
AND D2.C2 = D1.C2
AND D2.C3 = D1.C3
GROUP BY D1.C1, D2.C1,
D1.C2, D2.C2,
D1.C3, D2.C3

The table structure is:

CREATE TABLE TD_DATA1 (
C1 VARCHAR(20) CHARACTER SET WIN1251 NOT NULL COLLATE WIN1251,
C2 INTEGER NOT NULL,
C3 DATE NOT NULL,
D1 FLOAT NOT NULL);
CREATE INDEX IDX_TD_DATA1 ON TD_DATA1(C1,C2,C3);
-- 100,000 rows


CREATE TABLE TD_DATA2 (
C1 VARCHAR(20) CHARACTER SET WIN1251 NOT NULL COLLATE WIN1251,
C2 INTEGER NOT NULL,
C3 DATE NOT NULL,
D2 FLOAT NOT NULL);
CREATE INDEX IDX_TD_DATA2 ON TD_DATA2(C1,C2,C3);
-- 100,000 rows

My machine is Athlon X2 5600+, 2 GB, 90 GB free space, and the query never
finish, otherway is

SELECT
C1,
C2,
C3,
SUM(T1) T1,
SUM(T2) T2
FROM (
SELECT
D1.C1,
D1.C2,
D1.C3,
SUM(D1.D1) T1,
NULL T2
FROM TD_DATA1 D1
GROUP BY D1.C1, D1.C2, D1.C3

UNION ALL

SELECT
D1.C1,
D1.C2,
D1.C3,
NULL T1,
SUM(D1.D2) T2
FROM TD_DATA2 D1
GROUP BY D1.C1, D1.C2, D1.C3
) X
GROUP BY C1, C2, C3

and execute time is 1.203, someone explain me?

Thank for your advance



------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links