467,185 Members | 1,287 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,185 developers. It's quick & easy.

Functionally equivalent exception Join

Does DB2 UDB Supports Exception Join? I think 'No'. Hence I used the
following method to find out an functioanally equivalent exception join
select * from tab1
where not exists
(
select col1 from tab2 where tab1.col1 != col1
)

I have indexes on col1 on both tables and statistics are updated but
still it takes some to execute this query. tab1 has 7 Million rows and
tab2 has 10,000 rows.

Any suggestions to improve the performance of this query?

Thanks,
db2udbgirl.

Apr 4 '06 #1
  • viewed: 3699
Share:
3 Replies
Here is the access plan
Access Plan:
-----------
Total Cost: 4.99386e+06
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
DTQ
( 2)
4.99386e+06
33855.8
|
1
GRPBY
( 3)
4.99386e+06
33855.8
|
1.64536e+07
NLJOIN
( 4)
4.99211e+06
33855.8
/---+---\
3.05148e+06 5.392
IXSCAN FILTER
( 5) ( 6)
62147.5 105.186
33847.8 8
| |
3.05148e+06 134.8
INDEX: CARDP1IN TBSCAN
I2 ( 7)
105.13
8
|
134.8
SORT
( 8)
105.115
8
|
1348
IXSCAN
( 9)
104.104
8
|
1349
INDEX: CARDP1IN
I1

Apr 4 '06 #2
db2udbgirl wrote:
Does DB2 UDB Supports Exception Join? I think 'No'. Hence I used the
following method to find out an functioanally equivalent exception join
select * from tab1
where not exists
(
select col1 from tab2 where tab1.col1 != col1
)


Have you tried an ANSI outer join with a post-join filter:

select tab1.*
from tab1
left join tab2
on tab1.col1 = tab2.col1
where tab2.col1 is null;

Works in IDS, don't see why DB2 shouldn't support it.

Art S. Kagel
Apr 4 '06 #3
> select * from tab1
where not exists
(
select col1 from tab2 where tab1.col1 != col1
)

If this statement returns exactly your required result, I feel
something interesting.
Because, if tab2 have more than two rows with different values of col1,
this query always returns no row of tab1.
If col1 of tab2 have only one value, this query returned rows which are
tab1.col1 = tab2.col1.
And, tab2 is empty, it returns all rows of tab1.

So, following example would be returned same result.
SELECT tab1.*
FROM tab1
LEFT OUTER JOIN
tab2
ON tab1.col1 = tab2.col1
WHERE (SELECT COUNT(DISTINCT col1) FROM tab2) = 1
AND tab2.col1 IS NOT NULL
OR (SELECT COUNT(col1) FROM tab2) = 0;

Apr 5 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Peter L. Buschman | last post: by
3 posts views Thread by Greg Yasko | last post: by
1 post views Thread by Mike Brown | last post: by
22 posts views Thread by Kurien Mathew | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.