Kang Y Randomized algorithms for query optimization. PhD thesis, University of Wisconsin, Madison, Wis.  Home/Search   Document Not in Database   Summary   Related Articles   Check

....joins in each plan for different buffer sizes, and the timing of when these costs were updated. The values of various parameters of the randomized algorithms are also important to their implementation. For those, we adopted the setup that has been used for conventional query optimization [IK90, Kan91]. Recall that a state in parametric query optimization is a plan function. As we discussed in Sect. 4.3, the plans in different image partitions of the current plan function may have common subplans which can be shared. However, the overhead of maintaining a plan graph where sharing common ....

....algorithms that we have discussed in this paper represent a completely different style of optimization. It has already been shown that for small queries (approximately up to ten joins) dynamic programming is superior to randomized algorithms, whereas for large queries the opposite holds [Kan91]. In this section, we provide a preliminary discussion of how the two approaches compare when dealing with unknown run time parameters. We distinguish between the case of allowing essentially no run time optimization overhead, which we have called parametric query optimization and has been the ....

Kang Y Randomized algorithms for query optimization. PhD thesis, University of Wisconsin, Madison, Wis.

....obtain a random starting point for a local optimization we used our algorithm that generates plans at random in a uniform way, See section 5. 2) Instead of exhaustively searching all neighbors of a plan to detect whether or not it is a local minimum we used the definition of a r local minimum [Kan91] This method classifies a plan as local minimum if none of the r randomly selected (with repetition) neighbors has a lower cost. With r being equal to the number of neighbors of the plan. Note that since the plans are selected at random, and repetitions are therefor possible, a r local minimum ....

....reduce(T) return(minS) Figure 6: Simulated Annealing Like in II the Simulated Annealing algorithm starts at a random state. In our implementation we used our algorithm that generates plans at random in a uniform way, see Section 5.2. For SA specific parameters we used the parameters given by [Kan91] parameter value initial temperature T 0 2 cost of initial plan frozen T 1 and cost unchanged for 4 stages equilibrium 16 J visited states in current stage temperature reduction T new = T old 0:95 As extra stopping condition, the frozen condition) we added the number of plans ....

[Article contains additional citation context not shown here]

Y. C. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin-Madison, 1991. Technical report #1053.

....for DBS3 [ACV91] The APE tool provides accurate costs for query plan execution on the DBS3 prototype. In particular, the cost model has been calibrated towards this system architecture for nested loop joins [AKK93] The cost model for hash join is similar to that used for main memory databases in [Kan91]. Systems used. Our experiments were performed on a Silicon Graphics Challenge Series machine, with 6 processors running at 150MHz. Optimization algorithms were programmed in SWI Prolog [Wie92] 3 Cost distribution in search spaces This section presents our results on the cost distribution of ....

....of considering all possible graph splittings, we split the graphs by randomly selecting an edge at each step, which results in a random plan. Random walks in graphs have been widely studied see, for example, Rag90] In particular, if all nodes in a graph have equal degree, as is the case here [Kan91], then we are equally likely to be at any node of the graph after n steps, for a sufficiently large n, regardless of the starting point. In practice, however, the length of the walk seems too large to be used to generate a single uniformly distributed plan. Instead, we consider all plans visited ....

[Article contains additional citation context not shown here]

Y. C. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin-Madison, 1991. Technical report #1053.

....those questions for the class of acyclic queries those whose query graph, defined below, is acyclic. The answer to the second question has a direct application to randomized query optimization, as selection of a random item in the search space is a basic primitive for most randomized algorithms [SG88, Swa89b, Swa89a, IK90, IK91, Kan91, LVZ93, GLPK94]. 1 A B C D p 1 p 2 p 3 A B D C p 1 . p3 . p 2 . Phi Phi H H Phi Phi H H Phi Phi H H A D B C Theta p 1 p 3 . p 2 . Phi Phi H H Phi Phi H H Phi Phi H H Figure 1: Query graph and operator trees. Acceptable operator trees are subject to restrictions on which relations ....

Y. C. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin-Madison, 1991. Technical report #1053.

....accounts for cpu only and considered execution plans with only hash joins. In this paper we extend our previous experiments to assess the stability of the phenomenon observed. We use the same I O dominated cost model used at the University of Wisconsin in their randomized optimization work [IK90, Kan91]. We examine the impact of indices, changes on the statistical profiles of the catalogs, and the use of different join algorithms. For the problem of selecting a join order, the size of the space is exponential in the number of relations (see [GLPK95] for the exact size) When, in addition, a ....

....The II algorithm stops as soon as a predefined number of plans has been generated. The plan found with the lowest cost is returned as the result. Figure 1 shows the pseudo code of the II algorithm. To detect a local minimum the neighbors are not searched exhaustively but a r local minimum is used [Kan91], i.e. a plan is a local minimum if none of r randomly selected neighbors has a lower cost. Since the plans are selected at random, and repetitions are possible, a r local minimum is not guaranteed to test all neighbors. In the experiments r is set to the number of neighbors of a node. 3 ....

[Article contains additional citation context not shown here]

Y. C. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin-Madison, 1991. Technical report #1053.

....on the characteristics of the cost function over the graph and the connectivity of the latter as determined by the neighbors of each node. They have been studied extensively for query optimization, being mutually compared and also compared against dynamic programming [SG88, Swa89, IW87, IK90, Kan91] The specific results of these comparisons vary depending on the choices made regarding issues of the algorithms implementation and setup, but also choices made in other modules of the query optimizer, i.e. the Algebraic Space, the Method Structure Space, and the Cost Model. In general, ....

Y. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin, Madison, May 1991.

....of Volcano type optimizers is described in some detail. A key component in these optimizers is a MEMO structure that efficiently stores information about all alternatives explored [GCD 94] To generate a bushy space the following rule set if used. This set was also used in [BMG93, IW87, IK91, Kan91] Rule set RS B0: ffl Right Associativity: A . B) C ; A . B . C) ffl Left Associativity: A . B . C) A . B) C. ffl Commutativity: A . B ; B . A. The set is redundant, because we can drop Right Associativity (or Left Associativity) and still generate the same space. We use ....

Y. C. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin-Madison, 1991. Technical report #1053.

....queries 1 3 5 7 9 11 13 15 17 0.5 1.0 1.5 2.0 2.5 Figure 3 Relative Cost of Optimization to the presence of indexes, the decision of using (and selecting) materialized views had to be based on cost estimates. We have used an experimental framework similar to that in [IK90, INSS92, Kan91, Shi93] The machine used for the experiments was a DECstation 3100. The queries were tested with a randomly generated relation catalog where relation cardinalities ranged from 1000 to 100000 tuples, and the numbers of unique values in join columns varied from 10 to 100 of the corresponding ....

....a secondary index was 1 2, and the choice between a B tree and hashing secondary index were again uniformly random. As for join methods, we used block nested loops, merge scan, and simple and hybrid hash join [Sha86] In our experiment, only the cost for number of I O (page) accesses [IK90, Kan91, CS94] was accounted. The experimental result is shown in Figure 3. The cost of optimization is normalized with respect to the cost of optimizing a single query, as in the traditional optimizer. The effect of saving redundant work by our enumeration algorithm has resulted in a rather slow growth ....

[Article contains additional citation context not shown here]

Y. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin, Madison, May 1991.

....defined as a percentage value of this size) 4 RW could easily be implemented in CROQUE by generating a random value and then selecting the plan at the position specified by this value from the list of equivalent plans. 2.2. 2 Iterative Improvement (II) Iterative improvement (e.g. described in [Kan91] starts at a random state in the search space and improves the solution by repeatedly accepting downhill moves (visiting neighboured plans characterized by lower costs) until it reaches a minimum. This is assumed to be a local minimum, if after a given number of trials there has not been found a ....

....other direction is 5 pursued. The algorithm stops if further moves are not possible. The number of moves is used to control the probability of accepting uphill moves. Similar to II, SA is guaranteed to find a local minimum at least. 2.2. 4 Two Phase Optimization (2PO) Two phase optimization [Kan91] is a combination of the two strategies II and SA. In the first phase, II is used to find a local minimum and in the second phase SA searches the surroundings, being able to do some uphill moves (refer to Figure 2) costs z AE qz jz ff zOE y i 3 3 j Psi starting ....

Y.C. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin, Madison, October 1991.

....by a time limit or the maximum number of plans visited. RW could easily be implemented in CROQUE by generating a random value and then selecting the plan at the position specified by this value from the list of equivalent plans. Iterative Improvement (II) Iterative improvement (e.g. described in [13]) starts at a random state in the search space and improves the solution by repeatedly accepting downhill moves (visiting neighboured plans characterized by lower costs) until it reaches a minimum. This is assumed to be a local minimum, if after a given number of trials there has not been found a ....

....step, the other direction is pursued. The algorithm stops if further moves are not possible. The number of moves is used to control the probability of accepting uphill moves. Similar to II, SA is guaranteed to find a local minimum at least. Two Phase Optimization (2PO) Two phase optimization [13] is a combination of the two strategies II and SA. In the first phase, II is used to find a local minimum and in the second phase SA searches the surroundings, being able to do some uphill moves (refer to Figure 1) 2PO may be implemented for one dimensional search spaces by only prolonging the ....

Y.C. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin, Madison, October 1991.

....those questions for the class of acyclic queries those whose query graph, defined below, is acyclic. The answer to the second question has a direct application to randomized query optimization, as selection of a random item in the search space is a basic primitive for most randomized algorithms [SG88, Swa89b, Swa89a, IK90, IK91, Kan91, LVZ93, GLPK94]. Acceptable operator trees are subject to restrictions on which relations can be joined together, and counting them does not reduce, in general, to the enumeration of familiar classes of trees e.g. binary trees, trees representing equivalent C. Galindo Legaria was supported by an ERCIM ....

Y. C. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin-Madison, 1991. Technical report #1053.

....tested each query varying the number of materialized views available ranging from 0 to 6. Note that due to the presence of indexes, the decision of using (and selecting) materialized views had to be based on cost estimates. We have used an experimental framework similar to that in [IK90, INSS92, Kan91, Shi50] The details are in [CKPS94] The experimental result is shown in Figure 2. The cost of optimization is normalized with respect to the cost of optimizing a single query, as in the traditional optimizer. The effect of saving redundant work by our enumeration algorithm has resulted in a ....

Y. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin, Madison, May 1991.

....that we have discussed in this paper represent a completely different style of optimization. It has already been shown that for small queries (approximately up to ten to fifteen joins) dynamic programming is superior to randomized algorithms, whereas for large queries the opposite holds [Kan91] We claim that for parametric query optimization, the break even point between the two approaches is at much smaller queries, and that randomized algorithms offer the only reasonable solution in most cases. In support of the above claim, we note that dynamic programming or any of its heuristic ....

Y. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin, Madison, May 1991.

....Among all attributes participating in the query, 10 of attributes were chosen randomly and made either group by attributes or aggregation attributes. At least one attribute was assigned as the grouping and another as the aggregation column of the query. We borrowed the experimental framework from [IK90, INSS92, K91] and we review some of the important details of that framework here. A randomly generated relation catalog where relation cardinalities ranged from 1000 to 100000 tuples was used. The number of unique values in each column was between 10 to 100 of the cardinality of that relation. Each relation ....

....Either a relation was physically sorted, or there was a B Gammatree or a hashing primary index on the key attribute. For each nonkey attribute, there was a 50 probability of having a secondary index on that attribute. In our experiment, only the cost for number of I O (page) accesses [CS94, IK90, K91] was accounted. 5.2 Performance Metrics For each choice of the query size and the parameters of the experiment, we chose to report the following four quantities as indexes of comparison of the quality of plans produced (Figure 5) The average ratio represents the average of the ratio of cost of ....

Kang, Y., "Randomized Algorithms for Query Optimization", Ph.D. Thesis, University of Wisconsin, Madison, WI, April 1991.

....[ACV91] The APE tool provides accurate costs for query plan execution on the DBS3 prototype. In particular, the cost model has been calibrated towards this system architecture for nested loop joins [AKK93] The cost model we use for hash join is similar to that used for main memory databases in [Kan91], namely: fRg fSg) hash fRg move fSg comp F With fRg and fSg the sizes of the two relations in tuples. It is assumed that fRg fSg, denoting that the hash table is build on the smallest relation. Systems used. Our experiments were performed on a Silicon Graphics Challenge Series ....

....split the graphs by randomly selecting an edge at each step, which results in a random plan. Random walks in graphs have been widely studied see, for example, GJ74, Ald89, Rag90] In particular, if all nodes in a graph have equal degree, as is the case in the search space for acyclic queries [Kan91], then we are equally likely to be at any node of the graph after n steps, for a sufficiently large n, regardless of the starting point. In practice, however, the length of the walk seems too large to be used to generate a single uniformly distributed plan. Instead, we consider all plans visited ....

[Article contains additional citation context not shown here]

Y. C. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin-Madison, 1991. Technical report #1053.

....rules to visited plans, adding the results to the set if they are new. When no new plans can be generated, the complete search space for this set of transformations has been explored. For join reordering, the transformations commonly used (to generate a bushy space) are [BMG93, IW87, IK91, Kan91] Rule set RS B0: ffl Right Associativity: A . B) C ; A . B . C) ffl Left Associativity: A . B . C) A . B) C. ffl Commutativity: A . B ; B . A. The set is redundant, because we can drop Right Associativity (or Left Associativity) and still generate the same space. We use ....

Y. C. Kang. Randomized Algorithms for Query Optimization. PhD thesis, University of Wisconsin-Madison, 1991. Technical report #1053.

Online articles have much greater impact   More about CiteSeer.IST   Add search form to your site   Submit documents   Feedback  

CiteSeer.IST - Copyright Penn State and NEC