Left-deep trees
In response to an article on hinting, one reader asked me to describe what I meant by the terms “left-deep” and “bushy” when talking about optimization strategies.
To demonstrate the differences, I created a couple of stored outlines in a 9.2 database and used the OEM Outline Manager to generate the pictures that I’ve pasted below. To read a tree, walk around it in an anti-clockwise direction, ignoring nodes on the way down and ”operating” them on the way back up. (But don’t operate them as you go “under” them, only as you go up, passing them to their right).
Left Deep
Walk down to the bottom left corner: we start with a tablescan of t4, then use an index into t3, joining the tables with a nested loop. We scan table t2 to join it with a merge join, then use an index into t1 to join it using a nested loop.
Bushy
Again we head for the bottom-left corner, but in this case we start with a tablescan of t2 and use an index into t1 to join it with a nested loop join. Then we leave the join result set in suspension, and walk on round the tree to do a tablescan of t4, using an index into t3 to join it using a nested loop. This gives us a second we join result set, which we join to the first result set using a hash join.
As you can see, the typical left-deep tree will maximise the depth of the tree, with nothing but little twiglets branching to the right as you build it. The typical bushy tree will be stubbier with branchng bits sticking out all over the place.
출처 : http://jonathanlewis.wordpress.com/2007/01/24/left-deep-trees/
댓글 없음:
댓글 쓰기