This plan really looks as if Oracle should have done a nested loop into a but didn’t. If you want a plan that looks (at first sight) even more contrary, here’s the plan I got if I changed the single hint to be /*+ use_nl(a) */
You may be wondering why there seems to be a hash join into b when we’ve hinted a nested loop join – but the join order that Oracle is using is B -> A -> D -> C with a swap_join_inputs(c) swap_join_inputs(d), so b is never “the next table in the join order”. In this case it’s table a that ends up in a position to be the inner table of a nested loop join. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Here’s the plan I happened to get with my data, indexing, version (11.2.0.4), etc.: Only one of the tables a and b can be the first table in the final execution plan so one of them will be “the next table in the join order” at some point, so this hint will guarantee that one of the tables will be the inner table of a nested loop join.
#Sql scratchpad code
If you want to guarantee that a and b will be joined in that order by a nested loop you will have to work a lot harder with your hints – and almost certainly need to make use of the /+ leading() */ hint.Ĭonsider the following query (I’ll put the table creation code at the end of the article if you want to experiment):
The hint /*+ use_nl(a b) */ is a short-hand for the pair of hints /*+ use_nl(a) use_nl(b) */ it doesn’t say anything about whether a and b should be joined, or in what order.
#Sql scratchpad manuals
The misunderstanding is not entirely surprising since for many years the Oracle manuals seemed to suggest (in their examples) that the hint did have a multi-table meaning and it wasn’t until 10g that the manual gave an explicit statement of the single-table nature of the hint. Surprisingly, although I’ve explained it many times over the last couple of decades ( here’s one from 2006), I couldn’t find an explanation on my blog though I did find a blog note where I’d made a passing comment about the equivalent misunderstanding of the use_hash(a b) syntax. My comment was underscored by a fairly prompt response asking what the hint did mean.
In response to a recent lamentation from Richard Foote about the degree of ignorance regarding the clustering_factor of indexes I commented on the similar degree of ignorance regarding the syntax of a specific hint, namely use_nl(a b), pointing out that this does not mean “do a nested loop from a to b”.
0 kommentar(er)
