-
hash looks like a bad choice here -- two large tables approx the same size, this will give you a huge amount of LIO's
-
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
-
If these fields are not unique, then it would probably do the HASH.
Might help to get an analyze run on the two join columns.
-
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
I'm willing to be the the reality is choice 3.
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|