Identical views, identical indexes, different sorts... why?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Identical views, identical indexes, different sorts... why?

  1. #1
    Join Date
    Mar 2001
    Posts
    63
    Still haven't figured this one out yet.

    I have two views that are pretty much exactly the same thing except for the core tables. The join columns are set up exactly alike, and the indexes are set on the same exact columns. However, one view uses a hash join while the other still uses a sort merge.

    I have checked everything that I can think of, to no avail. Why would this be happening?

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    The Where clause is usually the strongest influence on how the query will be executed.

    Are you using the Cost based optimizer or the rule based optimizer? The cost based optimizer just chooses what it thinks is the easiest execution path based on the table and index statistics.

  3. #3
    Join Date
    Mar 2001
    Posts
    63
    I did a analyze on the core table of the view that was doing the sort merge, and after it was finished it was then using a hash join (and increasing speed of the view by 10x).

    Thanks for the help! This whole event was a great learning experience for me.

    To follow up though:

    What tables should I worry about analyzing?

    Is it possible to "over analyze" a database?


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