Query execution professor dr t y lin
Download
1 / 17

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


  • 140 Views
  • Uploaded on

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

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

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



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


  • 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


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.



ad