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.
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)
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).
Yeah, but as I see it, there are only 3 possibilities here:
The are unique
...and have unique indexes -> Oracle lost it, use hint
...and don't have indexes -> Yeah, analyzing should solve - or hint
They are not unique -> the query is wrong -> fix the query and the plan will likely be fixed