Query Processing and Optimization in Relational Databases, Lecture notes of Database Management Systems (DBMS)

Download Query Processing and Optimization in Relational Databases and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!

Chapter One

Query Processing and Optimization

(Reading in the Textbook: Chapters 18 & 19)

Outline

• Basics of Relational Data Model
• Translating SQL Queries into Relational Algebra
• Basic Algorithms for Executing Query Operations
• Using Heuristic in Query Operations
• Using Selectivity and Cost Estimates in Query Optimization
• Semantic Query Optimization

Relational Algebra

• Relational Algebra refers the basic set of operations for the formal relational model
• A sequence of relational algebra operations forms relational algebra expression
• In the slides:
  • σ and S are used interchangeably for SELECT
  • π and P are used interchangeably for PROJECT
  • ρ and q are used interchangeably for RENAME
  • ∪ and D are used interchangeably for UNION
  • ∩ and C are used interchangeably for INTERSEECTION
  • …

Basic Relational Algebra Operations

S (R)

• S (sigma) is used to denote the SELECT operator
• The selection condition is a Boolean expression specified on the attributes of relation R
• R is a relational algebra expression whose result is a relation; the simplest expression is just the name of a database relation
• The relation resulting from the SELECT operation has the same attributes as R
• The number of tuples in the resulting relation is always less than or equal to the number of tuples in R
• Select operation is commutative
• We can always combine a cascade of SELECT operations into a single SELECT operation with a conjunctive (AND) condition

The SELECT operation

Basic Relational Algebra Operations…

qS(B1, B2, .., Bn) (R) or qS (R) or q(B1, B2, .., Bn) (R)

• q (rhoi) is used to denote the RENAME operator
• S is the new relation name
• B1, B2,..,Bn are the new attribute names

The RENAME operation

• Are used to merge the elements of two sets in various ways including UNION, INTERSECTION, and SET DIFFERENCE
• Are binary operations: each is applied to two sets
• The relations must be union compatible: have the same degree (the same umber of attributes)and dom(Ai) = dom(Bi) for each attributes (each pair of corresponding attributes have the same domain)

Basic Relational Algebra Operations…

Set Theoric Operations

• R C S = S C R

• (R C S) C T = R C (S C T)

• A relation that includes all tuples that are in both R and S

Basic Relational Algebra Operations…

INTERSECTION

• R - S # S - R

• A relation that includes all tuples that are in R but not in S

Basic Relational Algebra Operations…

SET DIFFERENCE

• EQUIJOIN

• A JOIN that involves join conditions with equality comparisons only
• we always have one or more pairs of attributes that have identical values in every tuple
• NATURAL JOIN (*)
• Requires that the two join attributes have the sane name in both relations.
• Renaming is necessary if the join attributes are not identical
• Only tuples from R that have matching tuples in S –and vice versa- appear in the result
• Tuples without a matching tuple are eliminated from the JOIN result
• Tuples with null in the join attributes are also eliminated

Basic Relational Algebra Operations…

JOIN

• LEFT OUTER JOIN

• Keeps every tuple in the left relation R in R S; if no matching tuple is found in S, then the attributes of S in the join result are filled with null values.
• RIGHT OUTER JOIN
• Keeps every tuple in the right relation S in R S; if no matching tuple is found in R, then the attributes of R in the join result are filled with null values
• FULL OUTER JOIN
• Keeps all tuples in both the left and the right relations when no matching tuples are found, filling them with null values as needed

Basic Relational Algebra Operations…

OUTER JOIN

17

Query Optimization…

Syntax Checking

Validation

Translation

Relational Algebra Optimization

Strategy Selection

Code Generation

<--SQL Query --> Syntactically Correct SQL Query

--> Valid SQL Query --> Relational Algebra Query

--> Optimized Relational Algebra Query

--> Execution Plan

--> Code for Query Query Processing and Optimization

Translating SQL Queries into Relational

Algebra

• Example: SELECT ALL FROM Employee WHERE salary > 3000

• Ssalary =“3000 ” Employee

• SELECT name,age FROM Employee WHERE salary > (SELECT MAX(salary)) FROM Employee WHERE department=“IT”
• C= MAX salary (Sdepartment=“IT” Employee)
• Pname,age(Ssalary>C Employee)

Represent Relational Algebra by Query tree

• A query tree is a tree data structure that corresponds to an extended relational algebra expression. It represents the input relations of the query as leaf nodes of the tree, and it represents the relational algebra operations as internal nodes.
• An execution of the query tree consists of executing an internal node operation whenever its operands are available and then replacing that internal node by the relation that results from executing the operation.