- 117 Views
- Uploaded on
- Presentation posted in: General

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

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.