sIT ossIG J Last N 500 pages Ps 80 time S Relation 100 M 1000 - - PDF document

SLIDE 1

µossIG

J

sIT

Last

time

Relation

S

N 500 pages Ps 80 R M 1000pages Pr

100

Nested

LoopJoions

Simple

CPR M Nam

w R

• uter

Pageoriented

M N 1M

Block based

M1

m

w

K

buffer

Index

MTM.pp

indexaccess cost data access costumed

IB't tree

clustered

h.is

2 a

hashing iEs

Sort MergeJoins

3

Mtn

if

B

pages of

1

I o per

the largerrelation

matchingtuple

MTN

if

B

N

where N corresponds

to the

smallest

relation

today

Hash

Joins

General Join Conditions

Aggregates

Hash Joins

Use

a

hash function

h_to

create partitions of

both

relations

ThoshingtuildingTf

Match tuples only between the corresponding partitions

fprobingCmatching

SLIDE 2

B

buffers

RNS

h

hash fengion

i'j

f

re R

read

r

and

add

it

to buffer hCri

Is

and

aka

ness

for

1 1,2

K

Are Re

I

and

sa

w

manors At.gg read 5

and

probe AT using hzCs

if

march found

add

Lns

to the resuls

clear

hash table from memory to proceed

p

with

next

pair of

partitions

cost

a i F

t It7

i I

an

SLIDE 3

Mo

read every partition

• nce

in memory HT

w

h2Cth

Search

in

Si

• s

we

stream Ri

ea

cost

MTN

input

• capu

total

cost of

Hash Join 3

Mtn 44570719dg

Memory Requirements

Tuxen

for the largest panition of

the smaller relation

s

Impure page for the other relation

• utput page

a few pages

• f both metadatg

Fudge four

f

for example 1 1 04

if

h

uniform

size of

a partition

NB

1

B

FINI 12

B

EN

what

if

not enough memory

for

Si

to fit

in

apply

the

same algorith recursively

Memons

read

repartition

Si Ri

with

hz

he h

marching per subpartition

Mem

is enough

if

not

again recursion

SLIDE 4

what

if

we

have

more

memory

E

7E F

i IF

E

I

hashing S N 1N

sizeofCSI

hashing R

M 1M

sizeof

s

marching

M sizeofCRD

N

sizeofcsdtot

N

2siofcsdtse.fr

13 300

MINOO

3

1000 500

2

5001250

4500

1500

4300

07

N

500

it

13 600

read

Sonce

e

build hash table

5am

R

• nce

prob S

• n thefly

SLIDE 5

Hash Join

us

SMJ

cost

3cm1N

3cm 1N

mom req

BD

smaller

B

fm

larger

137

i

23

B Tooo

32 3km.ie

hN3

mtn 2GizeofCRd sieofCssl

3

MTN

B

N

MTN

M 1N

Sorted

• utput

if impure sorted

3cm 1N

M 1N

BIT

sensitive

to

data

spew

a

e

jynbug

b

inequality

joins

a

for

INLJ

we

need index

with all

attributes

in

join

conditions

sortlhogh

use combination of

all attributes

b

INLJ

w Bt The

not HashIndex

HJ SM

cannot work

Bloor NLT the best

approach

Sey Toulon IEXCEPT Get difference

sortingsort

STR

• n

all attributes

merging

discard duplicates

UNION

see difference

SLIDE 6

hashing

partition Rts

7 H S pan

probe

Corr

R poem

coe

N see difference

Intersection special

case of Join

Equality across

all

attributes

Aggregation

SELECT

AVG sae

FROM

E

SCAN

• nce

GROUP BY

Lage avg salary

hash

age

Lage salary

count

Sort

age

calculae runninginfo of

aggregation

• n the fly

if

we have an

index on LGroupby select where

can use

• nly

the index

tf

Battering

many thing in parallel toughto

estimate what is coshedby BP

SNLJ B N B N

LRV 7Sequential flooding

MRU

INLJ

sort the outer relation