Uh, what am I missing here? Why in the world is it using a hash to join these two indexes???

And why are you counting, anyway?

The only reason to do what you are doing is if they are 2 truly disjoint sets and you want to count the number of rows in the intersection. Actually, your query won't even do that.

T1
1
1
2
3

T2
1
1
3
3

Results
T1 T2
1 1
1 1
1 1
1 1
3 3
3 3

I'm thinking this is not a useful count.

Okay, so let's further assume they are both unique indexes.

I did a quick test on a 3 million and 9 million row table (that have no relationship) and hee's the plan:

SELECT STATEMENT Optimizer=CHOOSE (Cost=9742 Card=1 Bytes=10)
SORT (AGGREGATE)
MERGE JOIN (Cost=9742 Card=3082337 Bytes=30823370)
INDEX (FULL SCAN) OF XPKFLNG_TRANS (UNIQUE) (Cost=9574 Card=8944461 Bytes=44722305)
SORT (JOIN) (Cost=7323 Card=3082337 Bytes=15411685)
INDEX (FAST FULL SCAN) OF XPKINDVL (UNIQUE) (Cost=504 Card=3082337 Bytes=15411685)

It uses a merge join, which is exactly what I would expect, and it took ~ 2 minutes.

I'd try the USE_MERGE hint, although I'm still not sure wy it didn't do that to begin with - maybe because they're string PKs.

OR...(still thinking out loud), they are *not* unique indexes. If these fields are not unique, then it would probably do the HASH. But as I said before, if they are not unique, then you are not counting anything useful anyway. Try re-writing it as a MINUS or INTERSECT to get the right number (and a better plan).

- Chris