Functionally equivalent exception Join

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

db2udbgirl - 04 Apr 2006 19:43 GMT

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.

Reply to this Message

db2udbgirl - 04 Apr 2006 19:55 GMT

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

Reply to this Message

Art S. Kagel - 04 Apr 2006 21:00 GMT

> Does DB2 UDB Supports Exception Join? I think 'No'. Hence I used the
> following method to find out an functioanally equivalent exception join
[quoted text clipped - 4 lines]
> 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

Reply to this Message

Tonkuma - 05 Apr 2006 06:27 GMT

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

Reply to this Message