Query Optimization for Object-Relational Database Systems

1
Query Optimization for Object-Relational Database
Systems

• Navin Kabra
• Advisor Professor David J. DeWitt

2
Query Optimization is

• Important
• Difficult

3
Problem Areas

• Difficult to implement a new optimizer
• Difficult to extend/modify existing optimizer
• Difficult to debug optimizer bugs
• Optimizer doesnt always work

4
Difficult times ahead

• Complex Queries
• Decision support, OLAP, Mining, Terabytes of data
• Complex Data
• User-defined types (UDTs), User-defined
  functions/methods (UDFs)

5
Road Map

• Introduction
• The Opt Architecture
• Extensibility features of Opt
• Debugging Support
• Conclusions

6
OPT Basic Idea
Code Independent of Query Algebra
Well-Defined Interface
Code Dependent on Query Algebra
7
OPT Overview
Code provided with OPT
Search Strategy
Abstract Classes
RUNTIME BINDING
Code written by Optimizer Implementor
Derived Classes
8
Abstract Classes

• Operator class (logical algebra)
• Algorithm class (physical algebra)
• Generator classes (search space)

9
An Example Query
Select from Emp, Dept where Emp.name Lee
AND Emp.dno Dept.dno
10
An Operator Tree
11
An Access Plan
12
Operator Tree Access Plan
13
Tree Descriptors
14
Plan Descriptors
15
Operators and Algorithms
16
Expand - Example
17
Expand - continued
18
Expand - continued
19
Going from a Tree to a Plan
20
Generators
Search Strategy
Plan Generator
Operators
Algorithm
Tree Generator
HashJoin Gen
Select
Join
Index Gen
SelectPushdown
Index Scan
LeftDeep Gen
Hash Join
21
Example Merge Join
22
Example Merge Join - continued
23
Enforcing Sorts
24
Enforcing Sorts
25
System-R style search strategy
26
Changing the Search Strategy
Search Strategy
2PO
Bottom Up
SA
II
Transformative
Plan Generator
Operators
Algorithm
Tree Generator
HashJoin Gen
Select
Join
Index Gen
SelectPushdown
Index Scan
LeftDeep Gen
Hash Join
27
A Transformation
28
Another Transformation
29
Transformation-based Search Strategies

• Volcano-style
• Randomized
• Iterative Improvement
• Simulated Annealing
• Two-phase Optimization

30
Road Map

• Introduction
• The Opt Architecture
• Extensibility features of Opt
• Debugging Support
• Conclusions

31
Optimizer Components
Search Strategy
Search Strategy
2PO
Bottom Up
SA
II
Transformative
Plan Generator
Operators
Algorithm
Tree Generator
HashJoin Gen
Select
Join
Index Gen
SelectPushdown
Index Scan
LeftDeep Gen
Hash Join
Search Space
Query Algebra
32
Extending an Optimizer (Query Algebra)

• Add Operator and Algorithm classes
• Add Generator classes
• No changes to search strategy required

33
Adding an Algorithm
Search Strategy
Search Strategy
2PO
Bottom Up
SA
II
Transformative
Plan Generator
Operators
Algorithm
Tree Generator
Select
Join
Index Scan
HashJoin Gen
SelectPushdown
Index Gen
LeftDeep Gen
Hash Join
Search Space
Query Algebra
34
Adding an Operator
Search Strategy
Search Strategy
2PO
Bottom Up
SA
II
Transformative
Plan Generator
Operators
Algorithm
Tree Generator
Select
Join
HashJoin Gen
Index Scan
Index Gen
SelectPushdown
LeftDeep Gen
Hash Join
Search Space
Query Algebra
35
Extending an Optimizer (Search Strategy)

• Add new SearchStrategy class
• Possibly add new Generator classes
• No changes to Query Algebra component

36
Adding a Tree Generator
Search Strategy
Search Strategy
2PO
Bottom Up
SA
II
Transformative
Plan Generator
Operators
Algorithm
Tree Generator
SelectPushdown
HashJoin Gen
Select
Join
Index Gen
LeftDeep Gen
Index Scan
Hash Join
Search Space
Query Algebra
37
Extending an Optimizer (Search Space)

• Add or remove appropriate Generator classes
• No changes to Search Strategy
• No changes to Query Algebra

38
Adding a Search Strategy
Search Strategy
2PO
Search Strategy
SA
Bottom Up
II
Transformative
Plan Generator
Operators
Algorithm
Tree Generator
HashJoin Gen
Select
Join
Index Gen
SelectPushdown
Index Scan
LeftDeep Gen
Hash Join
Search Space
Query Algebra
39
Road Map

• Introduction
• The Opt Architecture
• Extensibility features of Opt
• Debugging Support
• Conclusions

40
Debugging Support

• Lots of time spent debugging the optimizer
• Non-optimal plans produced due to
• Bugs in enumeration logic
• Bugs in cost model

41
Debugging Support - continued

• Optimizer Implementor indicates expected plan
• Optimizer indicates why expected plan was not
  produced
• Sets automatic breakpoints (inside a debugger) at
  appropriate location
• Only for Bottom Up Search Strategy
• Partially useful for other search strategies

42
Bug in Cost Model
Expected Plan
Generated Plan
43
Bug in Plan Enumeration
Expected Plan
Generated Plan
Generated Tree
44
Bug in Tree Enumeration
Expected Plan
Generated Tree
45
Partial Plan Specification

• Optimizer Implementor does not want to specify
  full plan
• Allow specification of a plan fragment
• A plan tree with incomplete information (and
  Dont Cares)

46
Partial Plan - Join Order
47
Partial Plan - Index Select
48
Partial Plan - General
49
Partial Plan - Complex
50
Using Partial Plans

• Allow Breakpoints using Partial Plans
• Force Plans using Partial Plans (discussed next)

51
Force Plans Using Partial Plans

• Over-ride the Optimizer
• Useful because
• During cost model validation
• Debugging and tuning the system
• End user wants to force a plan
• Optimizer tries to produce a plan that matches
  the specification

52
Force Plans - DML
Select from Emp, Dept where Emp.name
Lee AND Dept.dno Emp.dno hint algorithm
hashjoin leftinput algorithm indexscan
53
Conclusions

• The Opt architecture simplifies
• implementation
• maintenance
• experimentation
• Techniques for debugging an optimizer
• very useful
• more work needed