why nested_loop (9i) and not Hash join (8i) ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: why nested_loop (9i) and not Hash join (8i) ?

  1. #1
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    why nested_loop (9i) and not Hash join (8i) ?

    We have a query that runs for 4 mins in 8i and hours! in 9i:

    Looking at the explain plans, I see that there is a very expensive nested loop in 9i, where as it does a hash join in 8i.
    Indexes are the same on both, but not used.

    Then I noticed that in our 9i the hash_join_enabled param was set to false (it is true in 8i)...so I altered the session to true.
    But it still does a nested loop.

    Any ideas ? What else can I look at ? Can I force hash join some how ?

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Look at /* USE_HASH */ hint to force hash join. Whats your init.ora compatible parm set to 81x or 90x ??

    May be its using 81x optimizer even in 9i ?
    Reddy,Sam

  3. #3
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    compatible=9.2.0

    What else would make the CBO select NESTED LOOPS instead of HASH JOIN ?

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    This could be affected by changes in init parameters, particularly those that affect the optimizers "opinion" on how fast it is to access indexes in particular ways.

    I'm thinking of optimizer_index_cost_adj, hash_area_size, sort_area_size for example, so you might look at what changes have occurred in your init parameters.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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