Functionally equivalent exception Join

[db2udbgirl]

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.

[db2udbgirl]

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

[Art S. Kagel]

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

[Tonkuma]

> 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;