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.

Query Execution Professor: Dr T.Y. Lin

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

• 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 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.

• 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.

• There are mainly two approaches for locating the tuples of a relation R:

• Table-scan

• Index-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.

• 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.

• 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 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

• 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.

• The three functions for implementation of physical operators are:

• Open

• GetNext

• Close

• This function starts the process of getting tuples.

• It initializes any data structures needed to perform the operation

• 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.

• This function ends the iteration after all tuples.

• It calls Close on any argument of the operator.