Query execution professor dr t y lin
This presentation is the property of its rightful owner.
Sponsored Links
1 / 17

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


  • 95 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Query Execution Professor: Dr T.Y. Lin

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 execution professor dr t y lin

Query ExecutionProfessor: Dr T.Y. Lin

Prepared by,

Mudra Patel

Class id: 113


Topics covered

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

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


Query compilation1

Query Compilation


Introduction to physical query plan operators

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

Scanning tables

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

  • Table-scan

  • Index-scan


Table 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

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

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


Query execution professor dr t y lin

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

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

Iterators for implementation of Physical Operators

  • The three functions for implementation of physical operators are:

  • Open

  • GetNext

  • Close


Query execution professor dr t y lin

Open:

  • This function starts the process of getting tuples.

  • It initializes any data structures needed to perform the operation


Getnext

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

Close:

  • This function ends the iteration after all tuples.

  • It calls Close on any argument of the operator.


Thank you

Thank You!!!


  • Login