That’s a good question, and it would be easy to spend a few hours checking what the optimizer may have done – especially since I would have to design a data set to emulate the original query. I have a note to myself explaining the circumstances when a swap will be considered – but the plan doesn’t show the information I need to decide whether any swaps were considered for this join order.

I think you might get some ideas by dropping columns, and a few more by rearranging the order of the indexes (using the “pseudo” index join method from the previous article).

The variation in conversion cost looks like an easier one to test – my first guess would be that it depends on the average column length of the column introduced, my second on the number of distinct keys. Both ideas would be quite easy to test in isolation.

It’s always possible that the 10053 trace has a number of costs in it that don’t get into the plan, though (that’s been an irritant with bitmap indexes for years) – so that might be the first place to look.

]]>By: David Aldridgehttps://jonathanlewis.wordpress.com/2010/12/07/index-join-3/#comment-38449
Wed, 08 Dec 2010 07:02:17 +0000http://jonathanlewis.wordpress.com/?p=4856#comment-38449I suppose one might try infering the costs of the various hash joins from the change in cost of the view on line 2 observed when selectively dropping columns out of the query.

What do you think accounts for the variation in cost of the BITMAP CONVERSION TO ROWIDS? Would that be based on the number of distinct values for the column, or the size of the index?

I thought I’d be able to point a link to an article I had written that would be the perfect answer to your question – but I haven’t written on yet.

Give me a couple of days.

]]>By: Scotthttps://jonathanlewis.wordpress.com/2010/12/07/index-join-3/#comment-38312
Tue, 07 Dec 2010 22:40:26 +0000http://jonathanlewis.wordpress.com/?p=4856#comment-38312How would the plan look if it did pass the last table through the five hashes? Do you have an example query that would do this?
]]>