Oracle Scratchpad

December 4, 2006

Thinking it through

Filed under: CBO,Execution plans,Hints — Jonathan Lewis @ 7:00 pm BST Dec 4,2006

In a posting on table order in the from clause, I explain how the optimizer doesn’t always have time to investigate every possible join order for a query, and point out that this means it is possible for a change in the order of the tables in your from clause to result in a change in the execution plan for the query.

One of the comments attached to the post asked the question:

 Is the order of the tables in a join relevant to the CBO ? Please clarify doubts.

The comment also pointed to a question posed on AskTom, describing it as a controversial article and source of doubt, where Tom says in reply to a question about joins:

The order of the tables is not relevant, that is the job of the CBO to figure out the best and most efficient join order.  The CBO doesn’t care if you say “from t1, t2” or “from t2, t1” same with the join condition.  It is free to try them all.

The rest of the thread on AskTom includes a few comments from other users about the use of hints, and a couple of special cases.

Now English is clearly not the first language of the person asking the question – so I think you can view the use of the expression “controversial” as having the more traditional (or old-fashioned) meaning of ‘having a different view point’ – but I find it hard to understand how anyone could read the article and ask me “if the join order is relevant” – the final paragraph of the article states (having explained the mechanism):

So, in theory and for perfectly sensible reasons, the optimizer can produce a different execution plan if you switch table orders in the from clause. But it may never happen to you.

I don’t think my opinion could be stated any more clearly – and I don’t think I could explain why I hold that opinion other than repeating the article; there is no point in asking me to “clarify doubts”.

Of course there is the question of why Tom Kyte made his comment. The answer is simple – he’s wrong, of course. Hang on, though, he was answering a question about a two-table join and it does say in my notes that a ‘real’ query probably needs 7 or 8 tables before the issue becomes visible. He does also say “it’s free to try them all” in other words the optimizer’s work is not fixed simply by the join order you specify. So there is a reason why his answer can be correct but – when viewed out of context – appear to contradict my comments.

If you read the whole Tom Kyte thread, you will also note that it evolves to discuss issues of hinting SQL and dealing with problems where you bypass the optimizer’s decisions – and sometimes you put tables in a specific order with an /*+ ordered */ hint because the table order matters… so the thread even appears to disagree with itself(!!) but it should be clear to the observant reader that the answer inevitably changes as the question changes.

So, if you want me to compare and contrast two articles to ‘clarify your doubts’ – please ensure that you digest the articles and explain carefully why you have those doubts. If your “doubt” seems to consist of “can’t be bothered to read properly” then I won’t be sympathetic.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Powered by WordPress.com.