Distributed Query Processing

1
Distributed Query Processing
2
Agenda

• Recap of query optimization
• Transformation rules for PD systems
• Memoization
• Queries in heterogeneous systems
• Query evaluation strategies
• Eddies

3
Introduction

• Alternative ways of evaluating a given query
• Equivalent expressions
• Different algorithms for each operation (Chapter
  13)
• Cost difference between a good and a bad way of
  evaluating a query can be enormous
• Example performing a r X s followed by a
  selection r.A s.B is much slower than
  performing a join on the same condition
• Need to estimate the cost of operations
• Depends critically on statistical information
  about relations which the database must maintain
• Need to estimate statistics for intermediate
  results to compute cost of complex expressions

4
Introduction (Cont.)

• Relations generated by two equivalent expressions
  have the same set of attributes and contain the
  same set of tuples, although their attributes may
  be ordered differently.

5
Introduction (Cont.)

• Generation of query-evaluation plans for an
  expression involves several steps
• Generating logically equivalent expressions
• Use equivalence rules to transform an expression
  into an equivalent one.
• Annotating resultant expressions to get
  alternative query plans
• Choosing the cheapest plan based on estimated
  cost
• The overall process is called cost based
  optimization.

6
Equivalence Rules

• 1. Conjunctive selection operations can be
  deconstructed into a sequence of individual
  selections.
• 2. Selection operations are commutative.
• 3. Only the last in a sequence of projection
  operations is needed, the others can be
  omitted.
• Selections can be combined with Cartesian
  products and theta joins.
• ??(E1 X E2) E1 ? E2
• ??1(E1 ?2 E2) E1 ?1? ?2 E2

7
Equivalence Rules (Cont.)

• 5. Theta-join operations (and natural joins) are
  commutative. E1 ? E2 E2 ? E1
• 6. (a) Natural join operations are associative
• (E1 E2) E3 E1 (E2 E3)(b)
  Theta joins are associative in the following
  manner (E1 ?1 E2) ?2? ? 3 E3 E1
  ?2? ?3 (E2 ?2 E3) where ?2
  involves attributes from only E2 and E3.

8
Pictorial Depiction of Equivalence Rules
9
Equivalence Rules (Cont.)

• 7. The selection operation distributes over the
  theta join operation under the following two
  conditions(a) When all the attributes in ?0
  involve only the attributes of one of the
  expressions (E1) being joined.
  ??0?E1 ? E2) (??0(E1)) ? E2
• (b) When ? 1 involves only the attributes of E1
  and ?2 involves only the attributes of E2.
• ??1??? ?E1 ? E2)
  (??1(E1)) ? (??? (E2))

10
Equivalence Rules (Cont.)

• 8. The projections operation distributes over the
  theta join operation as follows
• (a) if ? involves only attributes from L1 ?
  L2
• (b) Consider a join E1 ? E2.
• Let L1 and L2 be sets of attributes from E1 and
  E2, respectively.
• Let L3 be attributes of E1 that are involved in
  join condition ?, but are not in L1 ? L2, and
• let L4 be attributes of E2 that are involved in
  join condition ?, but are not in L1 ? L2.

11
Equivalence Rules (Cont.)

• The set operations union and intersection are
  commutative E1 ? E2 E2 ? E1 E1 ? E2 E2
  ? E1
• (set difference is not commutative).
• Set union and intersection are associative.
• (E1 ? E2) ? E3 E1 ? (E2 ?
  E3) (E1 ? E2) ? E3 E1 ? (E2 ? E3)
• The selection operation distributes over ?, ? and
  . ?? (E1 E2) ?? (E1)
  ??(E2) and similarly for ?
  and ? in place of Also ?? (E1
  E2) ??(E1) E2 and
  similarly for ? in place of , but not for ?
• 12. The projection operation distributes over
  union
• ?L(E1 ? E2) (?L(E1)) ?
  (?L(E2))

12
Multiple Transformations (Cont.)
13
Optimizer strategies

• Heuristic
• Apply the transformation rules in a specific
  order such that the cost converges to a minimum
• Cost based
• Simulated annealing
• Randomized generation of candidate QEP
• Problem, how to guarantee randomness

14
Memoization Techniques

• How to generate alternative Query Evaluation
  Plans?
• Early generation systems centred around a tree
  representation of the plan
• Hardwired tree rewriting rules are deployed to
  enumerate part of the space of possible QEP
• For each alternative the total cost is determined
• The best (alternatives) are retained for
  execution
• Problems very large space to explore, duplicate
  plans, local maxima, expensive query cost
  evaluation.
• SQL Server optimizer contains about 300 rules to
  be deployed.

15
Memoization Techniques

• How to generate alternative Query Evaluation
  Plans?
• Keep a memo of partial QEPs and their cost.
• Use the heuristic rules to generate alternatives
  to built more complex QEPs
• r1 r2 r3 r4

r4
Level n plans
Level 2 plans
r3
r3
x
Level 1 plans
r1 r2
r2 r3
r3 r4
r1 r4
r2 r1
16
Distributed Query Processing

• For centralized systems, the primary criterion
  for measuring the cost of a particular strategy
  is the number of disk accesses.
• In a distributed system, other issues must be
  taken into account
• The cost of a data transmission over the network.
• The potential gain in performance from having
  several sites process parts of the query in
  parallel.

17
Par dist Query processing

• The world of parallel and distributed query
  optimization
• Parallel world, invent parallel versions of
  well-known algorithms, mostly based on
  broadcasting tuples and dataflow driven
  computations
• Distributed world, use plan modification and
  coarse grain processing, exchange large chunks

18
Transformation rules for distributed systems

• Primary horizontally fragmented table
• Rule 9 The union is commutative E1 ? E2 E2
  ? E1
• Rule 10 Set union is associative. (E1 ? E2) ?
  E3 E1 ? (E2 ? E3)
• Rule 12 The projection operation distributes
  over union
• ?L(E1 ? E2) (?L(E1)) ? (?L(E2))
• Derived horizontally fragmented table
• The join through foreign-key dependency is
  already reflected in the fragmentation criteria

19
Transformation rules for distributed systems

• Vertical fragmented tables
• Rules Hint look at projection rules

20
Optimization in Par Distr

• Cost model is changed!!!
• Network transport is a dominant cost factor
• The facilities for query processing are not
  homogenous distributed
• Light-resource systems form a bottleneck
• Need for dynamic load scheduling

21
Simple Distributed Join Processing

• Consider the following relational algebra
  expression in which the three relations are
  neither replicated nor fragmented
• account depositor branch
• account is stored at site S1
• depositor at S2
• branch at S3
• For a query issued at site SI, the system needs
  to produce the result at site SI

22
Possible Query Processing Strategies

• Ship copies of all three relations to site SI
  and choose a strategy for processing the entire
  locally at site SI.
• Ship a copy of the account relation to site S2
  and compute temp1 account depositor at S2.
  Ship temp1 from S2 to S3, and compute temp2
  temp1 branch at S3. Ship the result temp2 to SI.
• Devise similar strategies, exchanging the roles
  S1, S2, S3
• Must consider following factors
• amount of data being shipped
• cost of transmitting a data block between sites
• relative processing speed at each site

23
Semijoin Strategy

• Let r1 be a relation with schema R1 stores at
  site S1
• Let r2 be a relation with schema R2 stores at
  site S2
• Evaluate the expression r1 r2 and obtain
  the result at S1.
• 1. Compute temp1 ? ?R1 ? R2 (r1) at S1.
• 2. Ship temp1 from S1 to S2.
• 3. Compute temp2 ? r2 temp1 at S2
• 4. Ship temp2 from S2 to S1.
• 5. Compute r1 temp2 at S1. This is the same as
  r1 r2.

24
Formal Definition

• The semijoin of r1 with r2, is denoted by
• r1 r2
• it is defined by
• ?R1 (r1 r2)
• Thus, r1 r2 selects those tuples of r1 that
  contributed to r1 r2.
• In step 3 above, temp2r2 r1.
• For joins of several relations, the above
  strategy can be extended to a series of semijoin
  steps.

25
Join Strategies that Exploit Parallelism

• Consider r1 r2 r3 r4 where
  relation ri is stored at site Si. The result must
  be presented at site S1.
• r1 is shipped to S2 and r1 r2 is computed at
  S2 simultaneously r3 is shipped to S4 and r3
  r4 is computed at S4
• S2 ships tuples of (r1 r2) to S1 as they
  produced S4 ships tuples of (r3 r4) to S1
• Once tuples of (r1 r2) and (r3 r4) arrive
  at S1 (r1 r2) (r3 r4) is computed
  in parallel with the computation of (r1 r2)
  at S2 and the computation of (r3 r4) at S4.

26
Query plan generation

• Apers-Aho-Hopcroft
• Hill-climber, repeatedly split the multi-join
  query in fragments and optimize its subqueries
  independently
• Apply centralized algorithms and rely on
  cost-model to avoid expensive query execution
  plans.

27
Query evaluators
28
Query evaluation strategy

• Pipe-line query evaluation strategy
• Evaluation
• Oriented towards OLTP applications
• Granule size of data interchange
• Items produced one at a time
• No temporary files
• Choice of intermediate buffer size allocations
• Query executed as one process
• Generic interface, sufficient to add the iterator
  primitives for the new containers.
• CPU intensive
• Amenable to parallelization

29
Query evaluation strategy

• Pipe-line query evaluation strategy
• Called Volcano query processing model
• Standard in commercial systems and MySQL
• Basic algorithm
• Demand-driven evaluation of query tree.
• Operators exchange data in units such as records
• Each operator supports the following interfaces
  open, next, close
• open() at top of tree results in cascade of opens
  down the tree.
• An operator getting a next() call may recursively
  make next() calls from within to produce its next
  answer.
• close() at top of tree results in cascade of
  close down the tree

30
Volcano Refresher
Try to maximize performance
Query SELECT name, salary.19 AS
tax FROM employee WHERE age gt 25
31
Volcano Refresher
Try to maximize performance

• Operators
• Iterator interface
• open()
• next() tuple
• close()

32
Volcano paradigm
Try to maximize performance

• The Volcano model is based on a simple pull-based
  iterator model for programming relational
  operators.
• The Volcano model minimizes the amount of
  intermediate store
• The Volcano model is CPU intensive and
  inefficient

33
MonetDB paradigm
Try to use simple a software pattern

• The MonetDB kernel is a programmable relational
  algebra machine
• Relational operators operate on array-like
  structures

34
Query evaluation strategy

• Materialized evaluation strategy
• Used in MonetDB
• Basic algorithm
• for each relational operator produce the
  complete intermediate result using materialized
  operands
• Evaluation
• Oriented towards decision support queries
• Limited internal administration and dependencies
• Basis for multi-query optimization strategy
• Memory intensive
• Amendable for distributed/parallel processing

35
Try to use simple a software pattern
MAL
MonetDB Server
36
Operator implementation
Try to use simple a software pattern

• All algebraic operators materialize their result
• Local optimization decisions
• Heavy use of code expansion to reduce cost
• 55 selection routines
• 149 unary operations
• 335 join/group operations
• 134 multi-join operations
• 72 aggregate operations

37
Micro-benchmark

• Keeping the query result in a new table is often
  too expensive

In milliseconds/10K Fixed cost in ms
38
Multi-column tapestry
ms
MonetDB/SQL
joins
Experiments ran on Athlon 1.4, Linux
39

• A column store should be designed from scratch to
  benefit from its characteristics
• Simulation of a column store on top of an n-ary
  system using the Volcano model does not work

40
Try to maximize performance
Present
Queryoptimizer
Potency
Paste
Execution Paradigm
DatabaseStructures
41
Try to avoid the search space trap

• Applications have different characteristics
• Platforms have different characteristics
• The actual state of computation is crucial
• A generic all-encompassing optimizer cost-model
  does not work

42
Try to disambiguate decisions
MAL
MAL

• Operational optimizer
• Exploit everything you know at runtime
• Re-organize if necessary

MonetDB Server
43
Try to disambiguate decisions

• Strategic optimizer
• Exploit the semantics of the language
• Rely on heuristics

MAL
MAL

• Operational optimizer
• Exploit everything you know at runtime
• Re-organize if necessary

MonetDB Server
44
Try to disambiguate decisions

• Tactical MAL optimizer
• No changes in front-ends and no direct human
  guidance
• Minimal changes in the engine

MAL
Tactical Optimizer
MAL
MonetDB Server
45
Try to disambiguate decisions

• Code Inliner. Constant Expression Evaluator.
  Accumulator Evaluations.
• Strength Reduction. Common Term Optimizer.
• Join Path Optimizer. Ranges Propagation.
  Operator Cost Reduction. Foreign Key handling.
  Aggregate Groups.

• Code Parallizer. Replication Manager.
• Result Recycler.
• MAL Compiler. Dynamic Query Scheduler.
  Memo-based Execution.
• Vector Execution.
• Alias Removal. Dead Code Removal. Garbage
  Collector.

46
Try to maximize performance
Present
Queryoptimizer
Potency
Paste
Execution Paradigm
DatabaseStructures
47
Execution paradigms
No data from persistent store to the memory trash

• The MonetDB kernel is set up to accommodate
  different execution engines
• The MonetDB assembler program is
• Interpreted in the order presented
• Interpreted in a dataflow driven manner
• Compiled into a C program
• Vectorised processing
• X100 project

48
MonetDB/x100
Combine Volcano model withvector
processing. All vectors together should fit the
CPU cache Vectors are compressed Optimizer
should tune this, given the query characteristics.
X100 query engine
CPU cache
ColumnBM (buffer manager)
RAM
networked ColumnBM-s
49
No data from persistent store to the memory trash

• Varying the vector size on TPC-H query 1

mysql, oracle, db2
low IPC, overhead
MonetDB
RAM bandwidth bound
X100
50
No data from persistent store to the memory trash

• Vectorized-Volcano processing can be used for
  both multi-core and distributed processing
• The architecture and the parameters are
  influenced heavily by
• Hardware characteristics
• Data distribution to compress columns

51
The proof of the pudding is in the eating

• Does MonetDB stand a real test?
• Is the main memory orientation a bottleneck?
• Is it functionally complete?

52
TPC-H
TPC-H 60K rows line_item table Comfortably fit
in memory Performance in milliseconds
ATHLON X2 3800 (2000mhz) 2 disks in raid 0, 2G
main memory
53
TPC-H
Scale-factor 1 6M row line-item table Out of the
box performance Queries produce emptyor
erroneous results
ATHLON X2 3800 (2000mhz) 2 disks in raid 0, 2G
main memory
54
TPC-H
ATHLON X2 3800 (2000mhz) 2 disks in raid 0, 2G
main memory
55
TPC-H
ATHLON X2 3800 (2000mhz) 2 disks in raid 0, 2G
main memory
56

• Code base for MonetDB/SQL is 1.2M lines of C
• Nightly regression testing on 17 platforms

57
Try to maximize performance
Present
Materialized Views
Potency
Paste
Cracking
B-tree, Hash Indices
58
Find a trusted fortune teller

• Indices in database systems focus on
• All tuples are equally important for fast
  retrieval
• There are ample resources to maintain indices
• MonetDB cracks the database into pieces based on
  actual query load

59
Cracking algorithms
Physical reorganization happens per column based
on selection predicates.
Split a piece of a column in two new pieces
Alt10
Alt10
Agt10
60
Cracking algorithms
Physical reorganization happens per column
Split a piece of a column in two new pieces
Split a piece of a column in three new pieces
Alt5
Alt10
Alt10
5ltAlt10
5ltAlt10
Agt10
Agt10
61
Cracking example
select Agt5 and Alt10
17
3
8
6
2
12
13
4
15
62
Cracking example
select Agt5 and Alt10
17
17
3
3
8
8
6
6
2
2
15
15
13
13
4
4
12
12
63
Cracking example
select Agt5 and Alt10
gt10
17
17
3
3
8
8
6
6
2
2
15
15
13
13
4
4
12
12
gt10
64
Cracking example
select Agt5 and Alt10
gt10
17
17
3
3
8
8
6
6
2
2
15
15
13
13
4
4
12
12
65
Cracking example
select Agt5 and Alt10
gt10
17
17
3
3
8
8
6
6
2
2
15
15
13
13
lt5
4
4
12
12
66
Cracking example
select Agt5 and Alt10
gt10
17
17
3
3
8
8
6
6
4
2
2
15
15
13
13
lt5
4
12
12
67
Cracking example
select Agt5 and Alt10
gt10
17
3
3
8
8
6
6
4
2
2
15
15
13
13
lt5
4
17
12
12
68
Cracking example
select Agt5 and Alt10
gt10
17
4
3
3
8
8
6
6
2
2
15
15
13
13
lt5
4
17
12
12
69
Cracking example
select Agt5 and Alt10
17
4
3
3
8
8
6
6
2
2
15
15
13
13
gt10
4
17
12
12
70
Cracking example
select Agt5 and Alt10
17
4
3
3
8
8
6
6
2
2
15
15
gt10
13
13
4
17
12
12
71
Cracking example
select Agt5 and Alt10
17
4
3
3
8
8
6
6
lt5
2
2
15
15
13
13
4
17
12
12
72
Cracking example
select Agt5 and Alt10
17
4
3
3
lt5
8
8
6
6
lt5
2
2
15
15
13
13
4
17
12
12
73
Cracking example
select Agt5 and Alt10
17
4
3
3
gt5 and lt10
8
8
6
6
lt5
2
2
15
15
13
13
4
17
12
12
74
Cracking example
select Agt5 and Alt10
17
4
3
3
gt5 and lt10
8
8
6
6
2
lt5
2
15
15
13
13
4
17
12
12
75
Cracking example
select Agt5 and Alt10
17
4
3
3
gt5 and lt10
8
6
6
2
lt5
2
8
15
15
13
13
4
17
12
12
76
Cracking example
select Agt5 and Alt10
17
4
3
3
gt5 and lt10
8
2
6
6
lt5
2
8
15
15
13
13
4
17
12
12
77
Cracking example
select Agt5 and Alt10
17
4
3
3
8
2
6
6
gt5 and lt10
2
8
15
15
13
13
4
17
12
12
78
Cracking example
select Agt5 and Alt10
17
4
lt 5
3
3
8
2
6
6
gt 5
2
8
15
15
13
13
gt 10
4
17
12
12
79
Cracking example
Improve data access for future queries
select Agt5 and Alt10
17
4
lt 5
3
3
8
2
6
6
gt 5
2
8
15
15
13
13
gt 10
4
17
12
12
80
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
lt 5
3
3
8
2
6
6
gt 5
2
8
15
15
13
13
gt 10
4
17
12
12
81
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
4
lt 5
lt 5
3
3
3
8
2
2
6
6
6
gt 5
gt 5
2
8
8
15
15
15
13
13
13
gt 10
gt 10
4
17
17
12
12
12
82
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
4
lt 5
lt 5
3
3
3
8
2
2
6
6
6
gt 5
gt 5
2
8
8
15
15
15
13
13
13
gt 10
gt 10
4
17
17
12
12
12
83
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
4
lt 5
lt 5
3
3
3
8
2
2
6
6
6
gt 5
gt 5
2
8
8
15
15
15
13
13
13
gt 10
gt 10
4
17
17
12
12
12
84
racking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
gt3 and lt14
17
4
4
lt 5
lt 5
3
3
3
lt3
8
2
2
6
6
6
gt 5
gt 5
2
8
8
15
15
15
13
13
13
gt 10
gt 10
4
17
17
12
12
12
85
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
gt3 and lt14
17
4
4
lt 5
lt 5
3
3
3
2
lt3
8
2
6
6
6
gt 5
gt 5
2
8
8
15
15
15
13
13
13
gt 10
gt 10
4
17
17
12
12
12
86
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
gt3 and lt14
17
4
lt 5
lt 5
3
3
3
2
lt3
8
2
4
6
6
6
gt 5
gt 5
2
8
8
15
15
15
13
13
13
gt 10
gt 10
4
17
17
12
12
12
87
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
gt3 and lt14
17
4
2
lt 5
lt 5
3
3
3
lt3
8
2
4
6
6
6
gt 5
gt 5
2
8
8
15
15
15
13
13
13
gt 10
gt 10
4
17
17
12
12
12
88
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
2
lt 5
lt 5
3
3
3
lt3
8
2
4
6
6
6
gt 5
gt 5
2
8
8
15
15
15
13
13
13
gt 10
gt 10
4
17
17
12
12
12
89
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
2
lt3
lt 5
3
3
3
8
2
4
gt 3
6
6
6
gt 5
gt 5
2
8
8
15
15
15
13
13
13
gt 10
gt 10
4
17
17
12
12
12
90
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
2
lt3
lt 5
3
3
3
8
2
4
gt 3
6
6
6
gt 5
gt 5
2
8
8
15
15
15
13
13
13
gt 10
gt 10
4
17
17
12
12
12
91
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
2
lt3
lt 5
3
3
3
8
2
4
gt 3
6
6
6
gt 5
gt 5
2
8
8
15
15
15
13
13
13
gt 10
gt 10
4
17
17
12
12
12
92
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
2
lt3
lt 5
3
3
3
8
2
4
gt 3
6
6
6
gt 5
gt 5
2
8
8
15
15
15
13
13
13
12
gt 10
gt 10
4
17
17
12
12
93
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
2
lt3
lt 5
3
3
3
8
2
4
gt 3
6
6
6
gt 5
gt 5
2
8
8
15
15
13
13
13
12
gt 10
gt 10
4
17
17
12
12
15
94
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
2
lt3
lt 5
3
3
3
8
2
4
gt 3
6
6
6
gt 5
gt 5
2
8
8
15
15
12
13
13
13
gt 10
gt 10
4
17
17
12
12
15
95
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
2
lt3
lt 5
3
3
3
8
2
4
gt 3
6
6
6
gt 5
gt 5
2
8
8
15
15
12
13
13
13
gt 10
gt 10
4
17
17
12
12
15
96
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
2
lt3
lt 5
3
3
3
8
2
4
gt 3
6
6
6
gt 5
gt 5
2
8
8
15
15
12
13
13
13
gt 10
gt 10
4
17
17
12
12
15
97
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
2
lt3
lt 5
3
3
3
8
2
4
gt 3
6
6
6
gt 5
gt 5
2
8
8
15
15
12
gt10
13
13
13
gt 10
4
17
17
gt 14
12
12
15
98
Cracking example
Improve data access for future queries
select Agt5 and Alt10
select Agt3 and Alt14
17
4
2
lt3
lt 5
3
3
3
8
2
4
gt 3
6
6
6
gt 5
gt 5
2
8
8
15
15
12
gt10
13
13
13
gt 10
4
17
17
gt 14
12
12
15
99
Cracking example
Improve data access for future queries
The more we crack the more we learn
select Agt5 and Alt10
select Agt3 and Alt14
17
4
2
lt3
lt 5
3
3
3
8
2
4
gt3
6
6
6
gt 5
gt 5
2
8
8
15
15
12
gt10
13
13
13
gt 10
4
17
17
gt 14
12
12
15
100
Design
The first time a range query is posed on an
attribute A, a cracking DBMS makes a copy of
column A, called the cracker column of A A
cracker column is continuously physically
reorganized based on queries that need to touch
attribute such as the result is in a contiguous
space For each cracker column, there is a
cracker index
Cracker Index
Cracker Column
101
Try to avoid useless investments
A simple range query
102
Try to avoid useless investments
TPC-H query 6
103
Try to avoid useless investments

• Cracking is easy in a column store and is part of
  the critical execution path
• Cracking works under high volume updates

104
Updates

• Base columns are updated as normally
• We need to update the cracker column and the
  cracker index
• Efficiently
• Maintain the self-organization properties
• Two issues
• When
• How

105
When to propagate updates in cracking

• Follow the workload to maintain
  self-organization
• Updates become part of query processing
• When an update arrives, it is not applied
• For each cracker column there is
• a pending insertions column
• and a pending deletions column
• Pending updates are applied only when a query
  needs the specific values

106
Updates aware select

• We extended the cracker select operator to apply
  the needed updates before cracking
• The select operator
• Search the pending insertions column
• Search the pending deletions column
• If Steps 1 or 2 find tuples run an update
  algorithm
• Search the cracker index
• Physically reorganize the cracker column
• Update the cracker index
• Return a slice of the cracker column

107
Merging
Insert a new tuple with value 9
7
The new tuple belongs to the blue piece
Start position 1 values gt1
9
2
10
29
Start position 4 values gt12
25
31
57
Start position 7 values gt35
42
53
108
Merging
Insert a new tuple with value 9
7
The new tuple belongs to the blue piece
2
Start position 1 values gt1
10
9
29
Pieces in the cracker column are ordered
Start position 5 values gt12
25
31
Tuples inside a piece are not ordered
57
Start position 8 values gt35
42
Shifting is not a viable solution
53
109
Merging by Hopping
9
Insert a new tuple with value 9
7
We need to make enough room to fit the new tuples
Start position 1 values gt1
2
10
29
Start position 4 values gt12
25
31
42
Start position 8 values gt35
53
57
110
Merge Gradually

• A query merges only the qualifying values, i.e.,
  only the values that it needs for a correct and
  complete result

Merge Completely
Merge Gradually
We avoid the large peaks but...
Average cost increases significantly
111
The Ripple
Touch only the pieces that are relevant for the
current query
112
The Ripple
Touch only the pieces that are relevant for the
current query
7
Start position 1 values gt1
2
10
29
Start position 4 values gt22
25
31
57
Start position 7 values gt35
42
53
113
The Ripple
Touch only the pieces that are relevant for the
current query
Select 7lt Alt 15
7
Start position 1 values gt1
2
10
29
Start position 4 values gt22
25
31
57
Start position 7 values gt35
42
53
114
The Ripple
Touch only the pieces that are relevant for the
current query
Select 7lt Alt 15
7
Pending insertions
Start position 1 values gt1
2
5
10
9
29
16
Start position 4 values gt22
25
35
31
57
Start position 7 values gt35
42
53
115
The Ripple
Touch only the pieces that are relevant for the
current query
Select 7lt Alt 15
7
Pending insertions
Start position 1 values gt1
2
5
10
9
29
16
Start position 4 values gt22
25
35
31
57
Start position 7 values gt35
42
53
116
The Ripple
Touch only the pieces that are relevant for the
current query
Select 7lt Alt 15
7
Pending insertions
Start position 1 values gt1
2
5
10
9
29
16
Start position 4 values gt22
25
35
31
57
Start position 7 values gt35
42
53
117
The Ripple
Touch only the pieces that are relevant for the
current query
Select 7lt Alt 15
7
Pending insertions
Start position 1 values gt1
2
5
10
9
29
16
Start position 4 values gt22
25
35
31
57
Start position 7 values gt35
42
53
118
The Ripple
Touch only the pieces that are relevant for the
current query
Select 7lt Alt 15
7
Pending insertions
Start position 1 values gt1
2
5
10
9
29
16
Start position 4 values gt22
25
35
31
Avoid shifting down non interesting pieces
57
Start position 7 values gt35
42
53
119
The Ripple
Touch only the pieces that are relevant for the
current query
Select 7lt Alt 15
7
Pending insertions
Start position 1 values gt1
2
5
10
9
29
16
Start position 4 values gt22
25
35
31
Avoid shifting down non interesting pieces
57
Start position 7 values gt35
42
53
120
The Ripple
Touch only the pieces that are relevant for the
current query
Select 7lt Alt 15
7
Pending insertions
Start position 1 values gt1
2
5
10
9
29
16
Start position 4 values gt22
25
35
31
Avoid shifting down non interesting pieces
57
Start position 7 values gt35
42
53
Immediately make room for the new tuples
121
The Ripple
Touch only the pieces that are relevant for the
current query
Select 7lt Alt 15
7
Pending insertions
Start position 1 values gt1
2
5
10
29
9
16
Start position 4 values gt22
25
35
31
Avoid shifting down non interesting pieces
57
Start position 7 values gt35
42
53
Immediately make room for the new tuples
122
The Ripple
Touch only the pieces that are relevant for the
current query
Select 7lt Alt 15
7
Pending insertions
Start position 1 values gt1
2
5
10
16
29
9
Start position 4 values gt22
25
35
31
Avoid shifting down non interesting pieces
57
Start position 7 values gt35
42
53
Immediately make room for the new tuples
123
The Ripple
Touch only the pieces that are relevant for the
current query
Select 7lt Alt 15
7
Pending insertions
Start position 1 values gt1
2
5
10
16
9
29
Start position 4 values gt22
25
35
31
Avoid shifting down non interesting pieces
57
Start position 7 values gt35
42
53
Immediately make room for the new tuples
124
The Ripple
Touch only the pieces that are relevant for the
current query
Select 7lt Alt 15
7
Pending insertions
Start position 1 values gt1
2
5
10
16
9
29
25
Start position 5 values gt22
35
31
Avoid shifting down non interesting pieces
57
Start position 7 values gt35
42
53
Immediately make room for the new tuples
125
The Ripple
Touch only the pieces that are relevant for the
current query
Select 7lt Alt 15
7
Pending insertions
2
Start position 1 values gt1
5
10
16
9
29
25
Start position 5 values gt22
35
31
Avoid shifting down non interesting pieces
57
Start position 7 values gt35
42
53
Immediately make room for the new tuples
126
The Ripple
Maintain high performance through the whole
query sequence in a self-organizing way
127
The Ripple
Merge Ripple
Maintain high performance through the whole
query sequence in a self-organizing way
Merge Gradually
Merge Completely