1 / 11

QUERY EXECUTION - PowerPoint PPT Presentation

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.

PowerPoint Slideshow about 'QUERY EXECUTION' - gibson

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

15.3

Nested-Loop Joins

By:

SaloniTamotia (215)

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

• Two kinds:

• Tuple-Based Nested Loop Join

• Block-Based Nested Loop Join

• Fits in the iterator framework.

• Allows us to avoid storing intermediate relation on disk.

Tuple-Based Nested-Loop Join

• Simplest variation of the nested-loop join

• Loop ranges over individual tuples

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

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.

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

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

• Assumptions:

• B(S) ≤ B(R)

• B(S) > M

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

• Number of disk I/O’s:

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

or

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

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