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?
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.
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?