Query execution
1 / 11

QUERY EXECUTION - PowerPoint PPT Presentation

  • Uploaded on

QUERY EXECUTION. 15.3 Nested-Loop Joins. By: Saloni Tamotia (215). Introduction to Nested-Loop Joins. Used for relations of any side. Not necessary that relation fits in main memory Uses “ One-and-a-half” pass method in which for each variation: One argument read just once.

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

PowerPoint Slideshow about 'QUERY EXECUTION' - gibson

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



Nested-Loop Joins


SaloniTamotia (215)

Introduction to nested loop joins
Introduction to Nested-Loop Joins

  • Used for relations of any side.

  • Not necessary that relation fits in main memory

  • Uses “One-and-a-half” pass method in which for each variation:

  • One argument read just once.

  • Other argument read repeatedly.

  • Two kinds:

    • Tuple-Based Nested Loop Join

    • Block-Based Nested Loop Join

Advantages of nested loop join

  • Fits in the iterator framework.

  • Allows us to avoid storing intermediate relation on disk.

Tuple based nested loop join
Tuple-Based Nested-Loop Join

  • Simplest variation of the nested-loop join

  • Loop ranges over individual tuples

Tuple based nested loop join1
Tuple-Based Nested-Loop Join

  • Algorithm to compute the Join R(X,Y) | | S(Y,Z)

    FOR each tuple s in S DO

    FOR each tuple r in R DO

    IF r and s join to make tuple t THEN

    output t

  • R and S are two Relations with r and s as tuples.

  • carelessness in buffering of blocks causes the use of T(R)T(S) disk I/O’s

Improvement modification

To decrease the cost

  • Method 1: Use algorithm for Index-Based joins

    • We find tuple of R that matches given tuple of S

    • We need not to read entire relation R

  • Method 2: Use algorithm for Block-Based joins

    • Tuples of R & S are divided into blocks

    • Uses enough memory to store blocks in order to reduce the number of disk I/O’s.

Block based nested loop join algorithm
Block-Based Nested-Loop Join Algorithm

  • Access to arguments is organized by block.

    • While reading tuples of inner relation we use less number of I/O’s disk.

  • Using enough space in main memory to store tuples of relation of the outer loop.

    • Allows to join each tuple of the inner relation with as many tuples as possible.

Block based nested loop join algorithm1
Block-Based Nested-Loop Join Algorithm


FOR each chunk of M-1 blocks of SDO

FOR each block b of RDO

FOR each tuplet of bDO

find the tuples of S in memory that join with t

output the join of t with each of these tuples

Block based nested loop join algorithm2
Block-Based Nested-Loop Join Algorithm

  • Assumptions:

    • B(S) ≤ B(R)

    • B(S) > M

      This means that the neither relation fits in the entire main memory.

Analysis of nested loop join
Analysis of Nested-Loop Join

  • Number of disk I/O’s:

    [B(S)/(M-1)]*(M-1 +B(R))


    B(S) + [B(S)B(R)/(M-1)]

    or approximately B(S)*B(R)/M