Query Processing

1
Query Processing

• B.Ramamurthy
• Chapter 12

2
Introduction

• Query processing refers to activities including
  translation of HLL queries into operations at
  physical file level, query-optimization
  transformations, and actual evaluation of
  queries.
• Objective of most query processing algorithms is
  to reduce the number of disk access which
  dominates the cost of query processing.

3
Query Processing Steps

• Parsing and translation checks the validity and
  translates to relational algebraic expression.
• Optimization analogous to algebraic
  simplification
• Evaluation many ways of evaluation.

4
Steps in Query Processing
Parser Translator
Relational Algebra Expressions
Query
Stats about data
Optimizer
Evaluation Engine
Query Output
Execution Plan
data
data
5
Query Processing Steps Example

• Select balance
• From account
• Where balance lt 2500
• Can be translated into either of the following
• s balance lt 2500 ( p balance (account))
• p balance (s balancelt 2500 (account))

6
Evaluation

• To specify how to evaluate the query we need to
  provide relational algebra expressions as well as
  annotate it with how to evaluate each operation.
• This annotation is known as evaluation primitive.
• Several primitives together form a pipeline.
• A sequence of primitives together form a
  query-execution plan or query-evaluation plan.
• Query execution engine evaluates the query.

7
A Query-evaluation Plan
p balance
s balancelt 2500
account
Different evaluation plans will have different
costs. It is Upto the system to decide the most
efficient plan.
8
Query Optimization

• Query optimization is the process of selecting
  the most efficient query evaluation plan for a
  query.
• Optimizers use statistical data about relations
  such as relation sizes and index depths to make a
  good estimate of cost of a plan.
• We will look at cost model, cost of individual
  operations selection, sorting, join, and other
  operations.

9
Catalog Information

• Relevant catalog information about relations
  includes
• nr , number of tuples in the relation
• br , number of blocks containing relation r
• sr , size of a tuple of relation r in bytes
• fr , blocking factor, number of tuples/block
• V(A,r), number of distinct values that appear in
  the relation r for attribute A.
• SC(A,r), selection cardinality of attribute A of
  relation r. Average number of records that
  satisfy equality condition for attribute A. 1 if
  A is key attribute, A(A,r) / nr for one
  distributed evenly.

10
Information about indices

• Information about the indices are also maintained
  in the statistical database
• fi , average fan out of internal nodes in a B
  tree index.
• HTi , number of levels of the index tree.
• LBi , number of lowest level index blocks in the
  index tree.
• All stats are usually registered for during DB
  updates.

11
Measures of Query Cost

• Disk access dominates the cost.
• Assume all transfers of blocks cost the same.
• Number of block transfers from the memory for
  query will be the measure we will use.
• Cost also depends on main memory buffer. We
  assume 1 block / relation main memory buffer.

12
Selection Operation

• Selection using linear search
• Selection using binary search
• .. Using indices primary and secondary
• Selection involving comparisons
• Implementation of complex selections

13
Join Operation

• There are several algorithms available for
  computing join.
• Lets analyze and compare them.

14
(No Transcript)
15
(No Transcript)