Randomized Query Optimization for Optimizing Large Join Queries

1
Randomized Query Optimization for Optimizing
Large Join Queries

• Yannis E. Ionnidis and Younkyung Cha Kang

2
The ProblemQuery Optimization

• Given
• A join query
• Find
• The optimal join strategy
• We can change
• order of joins
• methods of joins (e.g., nested-loops,merge-scan)

3
Example

• Join query A B C D
• Join strategies
• ((A B) C) D
• (A B) (C D)
• ((D C) B) A
• (A C) (B D)
• ...

4
Heuristic search

• Given
• A connected graph G(V,E)
• The cost function cost(v)
• Find
• A node v with the minimum cost(v)

5
Idea

• We need some assumptions about behavior of the
  cost function.
• Walk in the graph randomly preferring moves
  downhill. Find the minimum point in the path.

6
Iterative Improvement
DO NOT GO UPHILL
7
(No Transcript)
8
Iterative Improvement
9
Simulated Annealing

• We can go up by ?C with the probability
• pe-?C/T
• where T is a temperature.
• When ?C/T ranges from 0 to ?,
• p ranges from 1 to 0
• The temperature is reduced during walking

10
High Temperature
Low Temperature
11
Simulated Annealing
12
Two Phase Optimization

• Run II for a small period of time
• Run SA with a low initial temperature

13
How to apply these methods to our problem

• States access plans
• Neighbors
• Join method choice A (metod1)B ? A (metod2)B
  
• Join commutativity A B ? B A
• Join associativity (A B) C ? A (B C)
• Left join exchange (A B) C ? (A C) B
• Right join exchange A (B C) ? B (A C)
• Cost function accounts for the I/O required

• States access plans
• Neighbors
• Join method choice A (metod1)B ? A (metod2)B
  
• Join commutativity A B ? B A
• Join associativity (A B) C ? A (B C)
• Left join exchange (A B) C ? (A C) B
• Right join exchange A (B C) ? B (A C)
• Cost function accounts for the I/O required

14
A (B C)
A (C B)
(A B) C
(A C) B
Left join exchange
A (B C)
B (A C)
(A B) C
(B A) C
Right join exchange
15
Commutative rule

• Cost (A merge-scan B) Cost (B merge-scan A)
• Commutative rule can be eliminated.

Cost (A merge-scan B) Cost (B merge-scan
A) Commutative rule can be eliminated.
Cost (A merge-scan B) Cost (B merge-scan
A) Commutative rule can be eliminated.