Introduction
2
⚫In network andhierarchical DBMSs, low-level procedural
query language is generally embedded in high-level
programming language.
⚫Programmer’s responsibility to select most
appropriate execution strategy.
⚫With declarative languages such as SQL, user specifies what
data is required rather than how it is to be retrieved.
⚫Relieves user of knowing what constitutes good
execution strategy.
3.
What is query?
3
A query is a request for data or information from a database table
or combination of tables. This data may be generated as results
returned by Structured Query Language (SQL) or as pictorials,
graphs or complex results.
Queries help you find and work with your data
A query can either be a request for data results from your database
or for action on the data, or for both.
A query can give you an answer to a simple question,
perform calculations, combine data from different tables, add,
change, or delete data from a database.
4.
Types of query
4
Fourmain types of query
Append Query– takes the set results of a query and "appends"
(or adds) them to an existing table.
Delete Query– deletes all records in an underlying table from the
set results of a query.
Create Query– as the name suggests, it creates a table based on the
set results of a query.
Update Query – allows for one or more field in your table to be
updated.
5.
5
Query Processing
Activities involvedin retrieving data from the database.
Query Processing is a translation of high-level queries
into low-level expression.
Query Processing is the entire process of translating
a query into low level instructions in which the DBMS
can easily work with.
Aims of QP:
transform query written in high-level language (e.g. SQL),
into correct and efficient execution strategy expressed in
low-level language (implementing RA);
To choose efficient execution strategy to retrieve required
data.
6.
steps of query
processing
6
QueryProcessing involves five steps in DBMS
Step 1: Parsing.
Step 2: Translation.
Step 3: Optimizer.
Step 4: Execution
Plan. Step 5:
Evaluation.
7.
Cont.
The major stepsinvolved in query processing are depicted
in the figure below;
Query in high level
Evaluation engine
Optimizer
Parser
Execution plan
Translation
Result
Data dictionary
Data
Input
Output
1
5
4
3
2
7
8.
Cont.
Step 1: Parsing
The parser checks the syntax of the query, the user’s privileges
to execute the query, the table names and attribute names, etc.
8
9.
Cont.
9
Step 2: Translation
•If we have written a valid query, then it is converted
from high level language SQL to low level instruction
in Relational Algebra.
• SQL query can be converted into a Relational
Algebra equivalent expression
10.
Cont.
Step 3: Optimizer(Generation of Multiple Execution Plan)
Optimizer uses the statistical data stored as part of data
dictionary.
The statistical data are information about the size of the table,
the
length of records, the indexes created on the table, etc.
Optimizer also checks for the conditions and conditional
attributes which are parts of the query.
10
11.
Cont.
11
Step 4: ExecutionPlan
A query can be expressed in many ways.
The query processor module, at this stage, using the
information collected in step 3 to find different relational
algebra expressions.
This Execution plan accesses data from the database to give the
final
result.
12.
Cont.
Step 5: Evaluation/Execution
Though we got many execution plans constructed through
statistical data, though they return same result (obvious),
they differ in terms of Time consumption to execute the
query, or the Space required executing the query.
Hence, it is mandatory choose one plan which obviously
consumes less cost.
12
13.
Query Optimization
13
Activity ofchoosing an efficient execution strategy
for processing query.
Query optimization is the part of the query process in
which the database system compares different query
strategies and chooses the one with the least expected
cost.
As there are many equivalent transformations of same
high-level query to low level, aim of QO is to choose
one that minimizes resource usage.
Generally, reduce total execution time of query.
May also reduce response time of query.
14.
Cont.…
14
Query optimizationaims to minimize the cost of executing
the query, which can be measured by the time, the disk I/O,
the memory usage, or the network traffic.
Query optimization can be done statically, before executing
the query, or dynamically, during the execution
Common metrics for measuring query performance include
execution time, disk I/O, memory usage, and network
traffic.
15.
Query Languages
15
There aretwo kinds of query languages − relational
algebra and relational calculus.
Relational Algebra
Relational algebra is a formal language which
uses mathematical function to retrieve queries
It can be easily translated in SQL command.
It can be categorized as unary and binary
operator.
16.
Translating SQL Queriesinto Relational Algebra
SQL queries are translated into equivalent relational algebra
expressions before optimization.
Example
SELECT Ename FROM
Employee WHERE Salary >
5000;
Translated into Relational Algebra
Expression
σ Salary > 5000 (π Ename (Employee))
OR
π Ename (σ Salary > 5000 (Employee))
OR
16
17.
Translating SQL Queriesinto Relational Algebra
17
The fundamental operations of relational algebra
are as follows −
iii.
i. Select (σ)- unary
ii. project (π) -unary
Union
iv. Join
v. Set different
vi. Cartesian product
vii. intersection
viii. Rename -unary
18.
Cont.…
18
Basic operation
Selection(σ):select a subset of rows or records
from relation.
Projection (π) : discards unwanted columns or
tuples from
relation
Cartesian product(X): used to combine two
relations
Rename(p): used to rename relation or columns in
relation
Union(U): combine tuples in Reln1 and Reln2 by
taking
the tuples one if the tuples have the same value
Set difference(-): tuple in reln1 but not in reln2
Cont.…
20
Projection(π)= π projectioncondition(R)
it discards attributes that are not in projection list or condition
Defines a relation that contains a vertical subset of relation
Schema of the result is contains the fields in the projection
list or schema is not identical with the input relation
E.g. 1. Select sname, rating from S2
2. select age from S2
The first step is changing the SQL to relational algebra
Π sname, rating(S2)
Π age (S2)
Cont.…
22
E.g. Selection(σ) =σ selection condition (R)
-it selects rows that satisfy selection condition
-Schema of result is identical to schema of input relation
-Eg 1. select *from S2 where rating >8
2. select sname, rating from S2 where rating>8
σ rating>8(S2)
Πsname, rating(σ rating>8(S2))
sid sname rating age
28 yuppy 9 35
58 rusty 10 35
sname rating
yuppy 9
rusty 10
23.
Cont.…
23
Rename (p) rho
Is a unary operation
Used to rename the out of relation
P ss(S2) – rename the relation from S2 to SS
PM (Ssid, name) Π (Sid, sname ) (σ condition) rename both
relation name and column name changed
M Ssid name rating age
28 yuppy 9 35
58 rusty 10 35
24.
Cont.…
24
Binary RA operator(set operations)
Union ,Intersection ,Set-difference, Cartesian product
These operations are sometimes expensive to implement.
The CARTESIAN PRODUCT R X S (R with n records and j
attributes; S with m records and k attributes), for example, results in n *
m records and j + k attributes.
Hence it is important to avoid this operation and to substitute
other equivalent operations during query optimization
The three set operation ( UNION, INTERSECTION and
SET DIFFERENCE) apply only to union-compatible
relations
Relations that have the same number of attributes and
the same
attribute domains
25.
25
Approaches to QueryOptimization/Techniques
Using Heuristics: apply rules based on the form of the
query.
One of the main heuristic rules is to apply SELECT and
PROJECT operations before applying the JOIN or other binary
operations, because the size of the file resulting from a binary
operation such as
JOIN is usually a multiplicative function of the sizes of the input
files.
Heuristic is to first apply operations that reduce the size
(the cardinality or the degree) of the intermediate
relation.
That is:
a. Perform SELECTIONas early as possible:that will
reduce the cardinality (number of tuples) of the relation.
b. Perform PROJECTION as early as possible: that will reduce
the degree (number of attributes) of the relation.
The SELECT and PROJECT operations reduce the size of a file and
26.
Cont.…
26
Cost Estimates approach
Query optimizer estimate and compare the costs of executing
a query using different execution strategies and should choose
the strategy with the lowest cost estimate this approach is
called cost-based query optimization.
A cost-based query optimizer works as follows:
First, it generates all possible query execution plans.
Next, the cost of each plan is estimated. But cost is
estimated based on Cost Components for Query Execution
Finally, based on the estimation, the plan with the lowest
estimated cost is chosen.
27.
Cont.…
27
Cost Components forQuery Execution
Access Cost: Cost of searching for, reading, writing data blocks that
resides on secondary storage disk.
Storage Cost: Cost of storing intermediate files that are generated
by execution strategy for the query.
Computation Cost: Cost of performing in-memory operations on the
data buffers during query execution. It includes searching for, sorting,
merging, records, computing field values.
Communication Cost: Cost of shipping the query and its results
from database site to the site or terminal where it originated.
Memory Usage Cost: Cost pertaining to number of memory buffers
needed during query execution.
Main focus of Cost Based Optimization is minimizing
Communication Cost and Computation Cost.
28.
28
Cont.…
Notes:
For largedatabases, the main emphasis is on minimizing the access cost to
secondary storage, thus, different query strategies are compared in terms
of the number of block transfers between disk and main memory
For smaller databases, the emphasis is on minimizing computation cost
In distributed databases, communication cost must be minimized also.
Type of information needed in cost functions or Catalog Information used
in Cost Functions
To estimate the costs of various execution strategies, we must keep track
of any information that is needed for the cost functions
This information may be stored in the DMBS catalog, where it is accessed
by the query optimizer
Number of records (tuples) (r)
The average record size (R)
Number of blocks (b)
29.
29
cont.…
Semantic approach
SemanticQuery Optimization is a technique that uses
constraints specified on the database schema (Like unique
attributes and other more complex constraints)
Semantic query optimization is the process of using integrity
constraints and other semantic knowledge to transform query
into another
equivalent one with a lower execution cost.
Two queries are semantically equivalent(different vocabularies
but
same data meaning) if they return the same answer for a
database.
For this purpose it uses integrity constraints (column rule) to
match results (if they are equal in all case like cost, etc.)
30.
30
Cont.….
SELECT E.LastName, M.LastName
FROMEmployee AS E, Employee AS M
WHERE E.SuperSSN = M.SSN AND E.Salary > M.Salary
Suppose that we had a constraint on the database schema that stated that
no employee can earn more than his or her direct supervisor.
If the semantic query optimizer checks for the existence of this constraint, it
need not execute the query at all because it knows that the result of the
query will be empty.
This may save considerable time if the constraint checking can be
done efficiently.
However, searching through many constraints to find those that are
applicable to a given query and that may semantically optimize it can also
be quite time-consuming.
With the inclusion of active rules in database systems , semantic
query optimization techniques may eventually be fully incorporated
into the DBMSs of the future.
