I have 2 tables that are being joined together on a clustered index. The oracle optimizer decided to use a merge join instead of an index join. As a result the query took 1 1/2 hours to complete. I used a hint to force it to use an index join, and it took 0.48 seconds. Now the part that confuses is me is that when clustered indexes are involved, I would expect a merge join to be faster as did our optimizer. With a merge join, sorting the records is where you normally take a performance hit, but I would think you could skip the sorting step since the clustered indexes are already sorted. Any thoughts?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
index join vrs merge join
- Thread starter ddiamond
- Start date
Similar threads
- Locked
- Question