Simple query hangs.... Any idea..? - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Simple query hangs.... Any idea..?

  1. #11
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    hash looks like a bad choice here -- two large tables approx the same size, this will give you a huge amount of LIO's
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  2. #12
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #13
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #14
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width