Query Execution Professor: Dr T.Y. Lin

1 / 17

# Query Execution Professor: Dr T.Y. Lin - PowerPoint PPT Presentation

Query Execution Professor: Dr T.Y. Lin. Prepared by, Mudra Patel Class id: 113. Topics covered . Preview of Query Compilation Introduction to Physical-Query-Plan Operators Scanning tables Sorting while scanning tables Parameters for measuring costs I/O cost for scan operators

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Query Execution Professor: Dr T.Y. Lin' - evers

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Query ExecutionProfessor: Dr T.Y. Lin

Prepared by,

Mudra Patel

Class id: 113

Topics covered
• Preview of Query Compilation
• Introduction to Physical-Query-Plan Operators
• Scanning tables
• Sorting while scanning tables
• Parameters for measuring costs
• I/O cost for scan operators
• Iterators for implementation of Physical Operators
Query Compilation
• Query compilation is divided into 3 major steps:
• Parsing, in which a parse tree representing the query and its structure is constructed.
• Query rewrite, in which the parse tree is converted to an initial query plan, which is an algebraic representation of the query.
• Physical Plan Generation, where the abstract query plan is converted into physical query plan.
Introduction to Physical-Query-Plan Operators
• Physical query plans are built from the operators each of which implements one step of the plan.
• Physical operators can be implementations of the operators of relational algebra.
• However they can also be operators of non-relational algebra like ‘scan’ operator used for scanning tables.
Scanning tables
• There are mainly two approaches for locating the tuples of a relation R:
• Table-scan
• Index-scan
Table scan
• In this operation, Relation R is stored in secondary memory with its tuples arranged in blocks.
• It is possible to get the blocks one by one.
Index scan
• In index scan operation, if there is an index on any attribute of relation R, then we can use this index to get all the tuples of R.
Sorting while scanning tables
• Reasons why we need sorting while scanning tables:
• Various algorithms for relational-algebra operations require one or both of their arguments to be sorted relation
• the query could include an ORDER BY clause. Requiring that a relation be sorted

A Physical-query-plan operator sort-scantakes a relation R and a specification of the attributes on which the sort is to be made, and produces R in that sorted order.

• If we are to produce a relation R sorted by attribute a, and if there is a B-tree index on a, then index scan is used.
• If relation R is small enough to fit in main memory, then we can retrieve its tuples using a table scan.
Parameters for measuring costs
• Parameters that mainly affect the performance of a query are:
• The size of memory block on the disk and the size in the main memory affects the performance of a query.
• Buffer space availability in the main memory at the time of execution of the query.
• Size of input and the size of the output generated
I/O cost for scan operators
• This are the number of disk I/O’s needed for each of the scan operators.
• If a relation R is clustered, then the number of disk I/O’s is approximately B where B is the number of blocks where R is stored.
• If R is clustered but requires a two phase multi way merge sort then the total number of disk i/o required will be 3B.
• If R is not clustered, then the number of required disk I/0's is generally much higher.
Iterators for implementation of Physical Operators
• The three functions for implementation of physical operators are:
• Open
• GetNext
• Close
Open:
• This function starts the process of getting tuples.
• It initializes any data structures needed to perform the operation
GetNext:
• This function returns the next tuple in the result and adjusts the data structures as necessary to allow subsequent tuples to be obtained.
• If there are no more tuples to be returned, GetNext returns a special value NotFound.
Close:
• This function ends the iteration after all tuples.
• It calls Close on any argument of the operator.