The Memo Exploring Multiple Plans Ef ciently in Visual Basic .NET

Make UCC - 12 in Visual Basic .NET The Memo Exploring Multiple Plans Ef ciently

The core structure of the Query Optimizer is the Memo. This structure helps store the result of all the rules that are run in the Query Optimizer, and it also helps guide the search of possible plans to nd a good plan quickly and avoid searching a sub-tree more than once. This speeds up the compilation process and reduces the memory requirements. In effect, this allows the Query Optimizer to run more advanced optimizations compared to other optimizers without a similar mechanism. Although this structure is internal to the Query Optimizer, this section describes its basic operations so that you can better understand the way that the Query Optimizer selects a plan. The Memo stores operators from a query tree and uses logical pointers to represent the edges of that tree. If we consider the query SELECT * FROM (A INNER JOIN B ON A.a=B.b) AS D INNER JOIN C ON D.c=C.c, this can be drawn as a tree, as seen in Figure 8-16.

Join (D.c=C.c)

Join (A.a=B.b)

Get (C)

Get (A)

Get (B)

The same query stored in the Memo can be seen in Figure 8-17.

(Root) Group 4: 0 Join 3 2 Group 3: 0 Join 0 1 Group 2: 0 Table C Group 1: 0 Table B Group 0: 0 Table A

The Memo is made up a series of groups. When the Memo is rst populated, each operator is put into its own group. The references between operators are changed to be references to other groups in the Memo. In this model, it is possible to store multiple alternatives that yield the same result in the same group in the Memo. With this change, it is possible to search for the best sub-tree independently of what exists in higher-level groups in the Memo. Logical properties are stored within each memo group, and every additional entry in a group can share the property structure for that group with the initial alternative. One type of alternative explored by the Query Optimizer is join associativity. [(A join B) join C)] is equivalent to [A join (B join C)]. After this transformation is considered by the Query Optimizer, Figure 8-18 describes the updated Memo structure. (The bold sections are new.)

8 Group 5: 0 Join 1 2 (Root) Group 4: 1 Join 0 5 0 Join 3 2 Group 3: 0 Join 0 1 Group 2: 0 Table C Group 1: 0 Table B Group 0: 0 Table A

Notice how the new alternative ts into a structure. (B join C) that has not been previously seen in the Memo, so a new group is created that then references the existing groups for B and C. This representation saves a lot of memory when considering multiple possible query plans, and it makes it possible for the Query Optimizer to know if it has previously considered a section of the search space so that it can avoid doing that work again. (A join C) would be another valid alternative, though it is not shown. Rules are the mechanisms that allow the Memo to explore new alternatives during the optimization process. The join associativity example is implemented as an optimization rule that matches a speci c pattern and then creates a new alternative that is equivalent to the rst one (returns the same result for that portion of the query). The result of a rule, by de nition, can go into the same group as the root of the original pattern. An optimization search pass is split into two parts. In the rst part of the search, exploration rules match logical trees and generate new, equivalent alternative logical trees that are inserted into the Memo. Implementation rules run next, generating physical trees from the logical trees. Once a physical tree has been generated, it is evaluated by the costing component to determine the cost for this query tree. The resulting cost is stored in the Memo for that alternative. When all physical alternatives and their costs are generated for all groups in the Memo, the Query Optimizer nds the one query tree in the Memo that has the lowest cost and copies that into a stand-alone tree. The selected physical tree is very close to the showplan form of the tree. The optimization process is optimized further by using multiple search passes, separating the rules based on cost and how likely they are to be useful. There are three phases, and each phase runs a set of exploration and implementation rules. The phases are con gured to make small queries optimize quickly and to make more expensive queries consider more aggressive rewrite rules that may take longer to compile. For example, index matching is performed in the rst phase, whereas the matching of index view is generally not performed until a later stage. The Query Optimizer can quit optimization at the end of a phase if a suf ciently good