[PDF] - Age nda Que s t i ons on pha s e 2 of t he pr oj e PDF Document

SLIDE 1

1 DBM S I nt e r na l s Exe c ut i

n

a nd Opt i m i z a t i

n

M ay 10t h, 2004

Age nda

Que

s t i

ns
n

pha s e 2

f

t he pr

j

e c t

Today:

DBM S i nt e r na l s pa r t 2

– Que

r y e xe c ut i

n

– Que r y

pt

i m i z a t i

n
Ne

xt we e k:

– Thur s day, not M onday. – M os t l y Phi l Be r ns t e i n

n

m e t a

da

t a m anage m e nt .

Que r y Exe c ut i

n

Que r y c

m pi

l e r Exe c ut i

n

e ngi ne I nde x/ r e c

r

d m gr . Buf f e r m a na ge r St

r

a ge m a na ge r s t

r

a ge Us e r / Appl i c a t i

n

Que r y upda t e Que r y e xe c ut i

n

pl a n Re c

r

d, i nde x r e que s t s Pa ge c

m m a

nds Re a d/ wr i t e pa ge s

Que r y Exe c ut i

n

Pl a ns

Pur chase Per son

Buyer =nam e Ci t y=‘ seat t l e’ phone>’ 5430000’ buyer

( Si m pl e Nest ed Loops)

SELECT S.sname FROM Purchase P, Person Q WHERE P.buyer=Q.name AND Q.city=‘seattle’ AND Q.phone > ‘5430000’

s

Que r y Pl a n:

l
gi

c a l t r e e

i

m pl e m e nt a t i

n

c hoi c e a t e ve r y node

s

c he dul i ng

f
pe

r a t i

ns

.

( Tabl e scan) ( I ndex scan)

Som e

per

at

r

s ar e f r

m

r el at i

nal

al gebr a, and

t

her s ( e. g. , scan, gr

up)

ar e not .

The Le a ve s

f

t he Pl a n: Sc a ns

Ta

bl e s c a n:i t e r a t e t hr

ugh

t he r e c

r

ds

f

t he r e l a t i

n.
I

nde x s c a n:go t

t

he i nde x, f r

m

t he r e ge t t he r e c

r

ds i n t he f i l e ( whe n woul d t hi s be be t t e r ?)

Sor

t e d s c a n:pr

duc

e t he r e l a t i

n

i n

r

de r . I m pl e m e nt a t i

n

de pe nds

n

r e l a t i

n

s i z e .

How do we c

m bi

ne Ope r a t i

ns

?

The

i t e r a t

r

m ode l .Ea c h

pe

r a t i

n

i s i m pl em e nt e d by 3 f unc t i

ns

:

– Ope n: s e t s up t he da t a s t r uc t ur e s a nd pe r f

r

m s i ni t i a l i z a t i

ns

– Ge t Ne xt : r e t ur ns t he t he ne xt t upl e

f

t he r e s ul t . – Cl

s

e : e nds t he

pe

r a t i

ns

. Cl e a ns up t he da t a s t r uc t ur e s .

Ena

bl e s pi pe l i ni ng!

Cont

r a s t wi t h da t a

dr

i ve n m a t e r i a l i z e m ode l .

Som e

t i m e s i t ’ s t he s a m e ( e . g. , s

r

t e d s c a n) .

SLIDE 2

2 I m pl e m e nt i ng Re l a t i

na

l Ope r a t i

ns
W e

wi l l c

ns

i de r how t

i

m pl e m e nt :

– Se l e c t i

n (

) Se l e c t s a s ubs e t

f

r

ws

f r

m

r e l a t i

n.

– Pr

j

e c t i

n (

) De l e t e s unwant e d c

l

um ns f r

m

r e l a t i

n.

– Joi n ( ) Al l

ws

us t

com bi

ne t wo r e l a t i

ns

. – Se t

di

f f e r enc e Tupl e s i n r e l n. 1, but not i nr e l n. 2. – Uni

n Tupl

e s i n r e l n. 1 a nd i nr e l n. 2. – Aggr e gat i

n (

SUM , M I N,

e t c . ) a nd

GROUP BY

s

p > <

Sc he m a f

r

Exa m pl e s

Pur

c ha s e :

– Ea c h t upl e i s 40 byt e s l

ng,

100 t upl e s pe r page , 1000 page s ( i . e . , 100, 000 t upl e s , 4M B f

r

t he e nt i r e r e l a t i

n)

.

Pe

r s

n:

– Ea c h t upl e i s 50 byt e s l

ng,

80 t upl e s pe r page , 500 page s ( i . e , 40, 000 t upl e s , 2M B f

r

t he e nt i r e r e l a t i

n)

. Purchase (buyer:string, seller: string, product: integer), Person (name:string, city:string, phone: integer)

Si m pl e Se l e c t i

ns
Of

t he f

r

m

W i

t h no i nde x, uns

r

t e d: M us t e s s e nt i a l l y s c a n t he whol e r e l a t i

n;

c

s

t i s M ( #pa ge s i n R) .

W i

t h a n i nde x

n

s e l e c t i

n

a t t r i but e : Us e i nde x t

f

i nd qua l i f yi ng da t a e nt r i e s , t he n r e t r i e ve c

r

r e s pondi ng da t a r e c

r

ds . ( Ha s h i nde x us e f ul

nl

y f

r

e qua l i t y s el e c t i

ns

. )

Re

s ul t s i z e e s t i m a t i

n:

( Si z e

f

R) * r e duc t i

n

f a c t

r

. M or e

n

t hi s l a t e r .

SELECT * FROM Person R WHERE R.phone < ‘543%’

s R at

t r val ue

p

R

.

( )

Us i ng a n I nde x f

r

Se l e c t i

ns
Cos

t de pe nds

n

#qual i f yi ng t upl e s , a nd cl us t e r i ng.

– Cos t

f

f i ndi ng qua l i f yi ng da t a e nt r i e s ( t ypi c a l l y s m a l l ) pl us c

s

t

f

r e t r i e vi ng r e c

r

ds . – I n e xa m pl e , a s s um i ng uni f

r

m di s t r i but i

n
f

phone s , a bout 54%

f

t upl e s qua l i f y ( 500 pa ge s , 50000 t upl e s ) . W i t h a c l us t e r e d i nde x, c

s

t i s l i t t l e m or e t ha n 500 I / Os ; i func l us t e r e d, up t

50000

I / Os !

I

mpor t ant r e f i neme nt f

runc

l us t e r ed i nde x es :

1. Fi nd s

r

t t he r i d’ s

f

t he qua l i f yi ng da t a e nt r i e s . 2. Fe t c h r i ds i n

r

de r . Thi s e ns ur e s t ha t e a c h da t a pa ge i s l

oke

d a t j us t

nc

e ( t hough #

f

s uc h pa ge s l i ke l y t

be

hi ghe r t ha n wi t h c l us t e r i ng) .

Two Appr

a

c he s t

Ge

ne r a l Se l e c t i

ns
Fi

r s t a ppr

a

c h:Fi nd t he mos t s el e c t i v e ac c e s s pat h, r e t r i e ve t upl e s us i ng i t , a nd a ppl y any r em a i ni ng t e r m s t ha t don’ t m a t c h t he i nde x:

– M os t s e l e c t i ve ac ce s s pat h: An i nde x

r

f i l e s ca n t ha t we e s t i m a t e wi l l r e qui r e t he f e we s t pa ge I / Os . – Cons i de r c i t y=“s e at t l e AND phone <“543% ” :

A

ha s h i nde x

n c

i t y c an be us e d; t he n, phone<“ 543% ” m us t be c he c ke d f

r

e ac h r e t r i e ve d t upl e .

Si

m i l a r l y, a b- t r e e i nde x

n

phonecoul d be us e d; c i t y =“s e at t l e ” m us t t he n be c he cke d.

I nt e r s e c t i

n
f

Ri ds

Se

c

nd

a ppr

a

c h

– Ge t s e t s

f

r i ds

f

da t a r e cor ds us i ng e ac h m a t c hi ng i nde x. – The n i nt er s ec tt he s e s e t s

f

r i ds . – Re t r i e ve t he r e cor ds a nd appl y any r e m a i ni ng t e r m s .

SLIDE 3

3 I m pl e m e nt i ng Pr

j

e c t i

n
Two

pa r t s : ( 1) r e m ove unwa nt e d a t t r i but e s , ( 2) r e m ove dupl i c a t e s f r

m

t he r e s ul t .

Re

f i ne m e nt s t

dupl

i c a t e r e m ova l :

– I f a n i nde x

n

a r e l a t i

n

cont a i ns al l wa nt e d a t t r i but e s , t he n we ca n do a n i nde x

nl

ys ca n. – I f t he i nde x cont a i ns a s ubs e t

f

t he wa nt e d a t t r i but e s , you ca n r e m ove dupl i c a t e s l

c

al l y .

SELECT DISTINCT

R.name, R.phone

FROM Person R

Equa l i t y J

i

ns W i t h One J

i

n Col um n

R

S i s a c

m m on
pe

r a t i

n.

The c r

s

s pr

duc

t i s t

l

a r ge .He nc e , pe r f

r

m i ng R S a nd t he n a s e l e c t i

n

i s t

i

ne f f i c i e nt .

As

s um e : M pa ge s i n R,pR t upl e s pe r pa ge , N pa ge s i n S,pS t upl e s pe r pa ge .

– I n

ur

e xa m pl e s , R i s Pe r s

n

a nd S i s Pur c ha s e .

Cos

t me t r i c : #

f

I / Os . W e wi l l i gnor e

ut

put c

s

t s .

SELECT * FROM Person R, Purchase S WHERE R.name=S.buyer

> <

·

J OI N

Di s c us s i

n
How

woul d you i m pl em e nt j

i

n?

Si m pl e Ne s t e d Loops J

i

n

For

e a c h t upl e i n t he

ut

e rr e l a t i

n

R, we s c a n t he e nt i r ei nne r r e l a t i

n

S.

– Cos t : M + ( pR * M ) * N = 1000 + 100*1000*500 I / Os : 140 hour s !

Pa

ge

r

i e nt e d Ne s t e d Loops j

i

n: For e a c h pageof R, ge t e a c h page

f

S, a nd wr i t e

ut

m a t c hi ng pa i r s

f

t upl e s <r , s >, whe r e r i s i n R- pa ge a nd S i s i n S- pa ge .

– Cos t : M + M *N = 1000 + 1000*500 ( 1. 4 hour s )

For each tuple r in R do for each tuple s in S do if ri == sj then add <r, s> to result

I nde x Ne s t e d Loops J

i

n

I

f t he r e i s a n i nde x

n

t he j

i

n c

l

um n

f
ne

r e l a t i

n

( s a y S) ,c a n m a ke i t t he i nne r .

– Cos t : M + ( ( M *pR) * c

s

t

f

f i ndi ng m a t c hi ng S t upl e s )

For

e a c h R t upl e , c

s

t

f

pr

bi

ng S i nde x i s a bout 1. 2 f

r

ha s h i nde x, 2- 4 f

r

B+ t r e e . Cos t

f

t he n f i ndi ng S t upl e s de pe nds

n

c l us t e r i ng.

– Cl us t e r e d i nde x: 1 I / O ( t ypi c a l ) ,unc l us t e r e d: up t

1

I / O pe r m a t c hi ng S t upl e .

foreach tuple r in R do foreach tuple s in S where ri == sj do add <r, s> to result

Exa m pl e s

f

I nde x Ne s t e d Loops

Ha

s h- i nde x

n

nameof Pe r s

n

( as i nne r ) :

– Sc a n Pur c ha s e : 1000 pa ge I / Os , 100*1000 t upl e s . – For e a c h Pe r s

n

t upl e: 1. 2 I / Os t

ge

t da t a e nt r y i n i nde x, pl us 1 I / O t

ge

t ( t he e xa c t l y

ne

) m a t c hi ng Pe r s

n

t upl e . Tot a l : 220, 000 I / Os . ( 36 m i nut e s )

Ha

s h- i nde x

n

buy erof Pur c ha s e ( as i nne r ) :

– Sc a n Pe r s

n:

500 pa ge I / Os , 80*500 t upl e s . – For e a c h Pe r s

n

t upl e: 1. 2 I / Os t

f

i nd i nde x pa ge wi t h da t a e nt r i e s , pl us c

s

t

f

r e t r i e vi ng m a t c hi ng Pur c ha s e t upl e s . As s um i ng uni f

r

m di s t r i but i

n,

2. 5 pur c ha s e s pe r buye r ( 100, 000 / 40, 000) . Cos t

f

r e t r i e vi ng t he m i s 1

r

2. 5 I / Os de pe ndi ng

n

c l us t e r i ng.

SLIDE 4

4 Bl

c

k Ne s t e d Loops J

i

n

Us

e

ne

pa ge a s a n i nput buf f e r f

r

s c a nni ng t he i nne r S,

ne

pa ge a s t he

ut

put buf f e r , a nd us e a l l r e m a i ni ng page s t

hol

d ` ` bl

c

k’ ’

f
ut

e r R.

– For e a c h m a t c hi ng t upl e r i n R- bl

c

k, s i n S- page , a dd <r , s > t

r

e s ul t . The n r e ad ne xt R- bl

ck,

s c a n S, e t c .

. . . . . .

R & S

H as h t abl e f

r

bl

ck
f

R ( k < B- 1 page s ) I nput buf f er f

r

S O ut put buf f e r

. . .

Join Result

Sor t

M e

r ge J

i

n ( R S)

Sor

t R a nd S

n

t he j

i

n c

l

um n, t he n s c a n t he m t

do

a ` ` m e r ge ’ ’

n

t he j

i

n col um n.

– Adva nce s c a n

f

R unt i l c ur r e nt R- t upl e >= c ur r e nt S t upl e , t he n a dvance s c an

f

S unt i l c ur r e nt S- t upl e >= c ur r e nt R t upl e ; do t hi s unt i l c ur r e nt R t upl e = c ur r e nt S t upl e . – At t hi s poi nt , al l R t upl e s wi t h s am e val ue a nd al l S t upl e s wi t h s am e val ue mat c h;

ut

put <r , s > f

r

al l pa i r s

f

s uch t upl e s . – The n r e s um e s c a nni ng R a nd S.

> <

i=j

Cos t

f

Sor t

M e

r ge J

i

n

R

i s s c anne d

nce

; e a c h S gr

up

i s s ca nne d

nce

pe r m a t c hi ng R t upl e .

Cos

t : M l

g

M + N l

g

N + ( M +N)

But

r e al l y, we c a n do i t i n 3( M +N) wi t h s

m e

t r i c ke r y.

– The c

s

t

f

s c a nni ng, M +N, c

ul

d be M *N ( unl i ke l y! )

W i

t h 35, 100

r

300 buf f e r page s , bot h Pe r s

n

a nd Pur c ha s e c an be s

r

t e d i n 2 pa s s e s ; t

t

al : 7500. ( 75 s e c

nds

) .

Ha s h- J

i

n

Pa

r t i t i

n

bot h r e l a t i

ns

us i ng ha s h f n h: R t upl e s i n pa r t i t i

n

i wi l l

nl

y m a t c h S t upl e s i n pa r t i t i

n

i .

v Read in a partition

f R, hash it using

h2 (<> h!). Scan matching partition

f S, search for

matches.

Part i t i

ns
f

R & S

I nput buf f e r f

rSi

H as h t abl e f

r

par t i t i

n

Ri ( k < B- 1 page s )

B m ai n m em ory buf f ers Di s k

O ut put buf f e r

Di s k Joi n Res ul t

has h f n

h2

B m ai n m em ory buf f ers Di s k Di s k O ri gi nal Rel at i

n

O UTPUT 2 I NPUT 1 has h f unc t i

n

h

B- 1

Part i t i

ns

1 2 B- 1

. . .

Cos t

f

Ha s h- J

i

n

I

n pa r t i t i

ni

ng phas e , r e a d+wr i t e bot h r e l a t i

ns

; 2( M +N) . I n m a t c hi ng phas e , r e a d bot h r e l a t i

ns

; M +N I / Os .

I

n

ur

r unni ng e xam pl e , t hi s i s a t

t

al

f

4500 I / Os . ( 45 s e c

nds

! )

Sor

t

M e

r ge J

i

n vs . Ha s h J

i

n: – Gi ve n a m i ni m um am ount

f

m e m or y bot h have a cos t

f

3( M +N) I / Os . Has h J

i

n s upe r i

r
n

t hi s count i f r e l a t i

n

s i ze s di f f e r gr e a t l y. Al s

,

Ha s h J

i

n s hown t

be

hi ghl y pa r al l e l i za bl e . – Sor t

M e

r ge l e s s s e ns i t i ve t

da

t a s ke w; r e s ul t i s s

r

t e d.

Doubl e Pi pe l i ne d J

i

n ( Tukwi l a )

Ha s h J

i

n

8 Pa r t i a l l y pi pe l i ne d: no

ut

put unt i l i nne r r e a d 8 As ym m e t r i c ( i nne r vs .

ut

e r ) —

pt

i m i z a t i

n

r e qui r e s s

ur

c e be ha vi

r

knowl e dge

Doubl e Pi pe l i ne d Ha s h J

i

n

4 Out put s da t a i m m e di a t e l y 4 Sym m e t r i c — r e qui r e s l e s s s

ur

c e knowl e dge t

pt

i m i z e

SLIDE 5

5 Que r y Opt i m i z a t i

n

Di s c us s i

n
How

woul d you bui l d a que r y

pt

i m i z e r ?

Que r y Opt i m i z a t i

n

Pr

c

e s s ( s i m pl i f i e d a bi t )

Pa

r s e t he SQL que r y i nt

a

l

gi

c al t r e e:

– i de nt i f y di s t i nc t bl

c

ks ( c

r

r e s pondi ng t

ne

s t e d s ubque r i e s

r

vi e ws ) .

Que

r y r e wr i t e pha s e :

– a ppl y a l ge br a i c t r a ns f

r

m a t i

nst
yi

e l d a c he a pe r pl a n. – M e r ge bl

c

ks a nd m ove pr e di c a t e s be t we e n bl

c

ks .

Opt

i m i z e e a c h bl

c

k: j

i

n

r

de r i ng.

Com pl

e t e t he

pt

i m i z a t i

n:

s e l e c t s c he dul i ng ( pi pe l i ni ng s t r a t e gy) .

Bui l di ng Bl

c

ks

Al

ge br a i c t r a ns f

r

m a t i

ns

( m a ny and wa c ky) .

St

a t i s t i c a l m ode l : e s t i m a t i ng c

s

t s a nd s i z e s .

Fi

ndi ng t he be s t j

i

n t r e e s :

– Bot t

m -

up ( dynam i c pr

gr

am m i ng) : Sys t e m - R

Ne

we ra r c hi t e c t ur e s :

– St a r bur s t : r e wr i t e and t he n t r e e f i nd – Vol c a no: al l a t

nce

, t

p-

down.

Key Le s s

ns

i n Opt i m i z a t i

n
The

r e a r e m a ny a ppr

a

c he s a nd m a ny de t a i l s t

c
ns

i de r i n que r y

pt

i m i z a t i

n

– Cl a s s i c s e a r ch/

pt

i m i za t i

n

pr

bl

e m ! – Not com pl e t e l y s

l

ve d ye t !

M a

i n poi nt s t

t

ake a way a r e :

– Al ge br a i c r ul e s a nd t he i r us e i n t r ans f

r

m a t i

ns
f

que r i e s . – De c i di ng

n

j

i

n

r

de r i ng: Sys t e m - R s t yl e ( Se l i nge r s t yl e )

pt

i m i z a t i

n.

– Es t i m a t i ng cos t

f

pl a ns a nd s i ze s

f

i nt e r m e di a t e r e s ul t s .

Ope r a t i

ns

( r e vi s i t e d)

Sc

a n ( [ i nde x] , t a bl e , pr e di c a t e ) :

– Ei t he r i nde x s c an

r

t a bl e s ca n. – Tr y t

pus

h down s ar gabl epr e di c a t e s .

Se

l e c t i

n

( f i l t e r )

Pr
j

e c t i

n

( a l ways ne e d t

go

t

t

he da t a ?)

J
i

ns : ne s t e d l

op

( i nde xe d) , s

r

t

m e

r ge , ha s h,

ut

e r j

i

n.

Gr
upi

ng a nd a ggr ega t i

n

( us ua l l y t he l a s t ) .

SLIDE 6

6 Al ge br a i c La ws

Com m ut

a t i ve a nd As s

c

i a t i ve La ws

– R U S = S U R, R U ( S U T) = ( R U S) U T – R ∩ S = S ∩ R, R ∩ ( S ∩ T) = ( R ∩ S) ∩ T – R S = S R, R ( S T) = ( R S) T

Di

s t r i but i ve La ws

– R ( S U T) = ( R S) U ( R T) >< >< >< >< >< >< >< >< ><

Al ge br a i c La ws

Laws

i nvol vi ng s e l e c t i

n:

– s C

AND C’

( R) = s C( s C’ ( R) ) = s C( R) ∩ s C’ ( R) – s C

OR C’

( R) = s C( R) U s C’ ( R) – s C ( R S) = s C ( R) S

W he

n C i nvol ve s

nl

y a t t r i but e s

f

R

– s C ( R –S) = s C ( R) –S – s C ( R U S) = s C ( R) U s C ( S) – s C ( R ∩ S) = s C ( R) ∩ S >< ><

Al ge br a i c La ws

Exa

m pl e : R( A, B, C, D) , S( E, F, G)

– s F=3 ( R S) = ? – s A=5

AND G=9

( R S) = ? ><

D=E

><

D=E

Al ge br a i c La ws

Laws

i nvol vi ng pr

j

e c t i

ns

– P M ( R S) = P N( P P( R) P Q( S) )

W he

r e N, P, Q a r e a ppr

pr

i a t e s ubs e t s

f

a t t r i but e s

f

M

– P M ( P N( R) ) = P M ,

N(

R)

Exa

m pl e R( A, B, C, D) , S( E, F, G)

– P A,

B, G(

R S) = P ? ( P ?( R) P ?( S) ) >< >< >< ><

D=E D=E

Que r y Re wr i t e s : Sub- que r i e s

SELECT Em p. Na m e FROM Em p W HERE Em p. Age < 30 AND Em p. De pt # I N ( SELECT De pt . De pt # FROM De pt W HERE De pt . Loc = “Se a t t l e ” AND Em p. Em p#=De pt . M gr )

The Un- Ne s t e d Que r y

SELECT Em p. Na m e FROM Em p, De pt W HERE Em p. Age < 30 AND Em p. De pt #=De pt . De pt # AND De pt . Loc = “ Se a t t l e ” AND Em p. Em p#=De pt . M gr

SLIDE 7

7 Conve r t i ng Ne s t e d Que r i e s

Se l e c tdi s t i nc tx. nam e , x. m ake r Fr

m pr
duc

t x W he r ex. col

r

= “ bl ue ” AND x. pr i ce >= ALL ( Se l e c ty. pr i ce Fr

m pr
duc

t y W he r ex. m ake r = y. m a ke r AND y. col

r

=“ bl ue ” )

Conve r t i ng Ne s t e d Que r i e s

Se l e c tdi s t i nc tx. na m e , x. m a ke r Fr

m pr
duc

t x W he r ex. c

l
r

= “ bl ue ” AND x. pr i c e < SOM E ( Se l e c ty. pr i c e Fr

m pr
duc

t y W he r ex. m a ke r = y. m a ke r AND y. c

l
r

=“ bl ue ” )

Le t ’ s com put e t he com pl e m e nt f i r s t :

Conve r t i ng Ne s t e d Que r i e s

Se l e c t di s t i nc tx. na m e , x. m a ke r Fr

m pr
duc

t x, pr

duc

t y W he r ex. c

l
r

= “ bl ue ” AND x. m ake r = y. m ake r AND y. col

r

=“ bl ue ” AND x. pr i c e < y. pr i c e

Thi s

ne

be com e s a SFW que r y:

Thi s r e t ur ns e xa c t l y t he pr

duc

t s we DON’ T wa nt , s

…

Conve r t i ng Ne s t e d Que r i e s

( Se l e c tx. nam e , x. m a ke r Fr

m pr
duc

t x W he r ex. col

r

= “ bl ue ” ) EXCEPT ( Se l e c tx. nam e , x. m a ke r Fr

m pr
duc

t x, pr

duc

t y W he r ex. col

r

= “ bl ue ” AND x. m ake r = y. m a ke r AND y. col

r

=“ bl ue ” AND x. pr i ce < y. pr i ce )

Se m i

J
i

ns , M a gi c Se t s

You

c a n’ t a l wa ys un- ne s t s ub- que r i e s ( i t ’ s t r i c ky) .

But

you c a n

f

t e n us e a s e m i

j
i

n t

r

e duc e t he c

m put

a t i

n

c

s

t

f

t he i nne r que r y.

A

m a gi c s e t i s a s upe r s e t

f

t he pos s i bl e bi ndi ngs i n t he r e s ul t

f

t he s ub- que r y.

Al

s

c

al l e d “s i de wa ys i nf

r

m a t i

n

pa s s i ng” .

Gr

e at i de a; r e i nv e nt e d e v e r y f e w y e ar s

n

a r e gul ar bas i s .

Re wr i t e s : M a gi c Se t s

Cr e a t e Vi e w De pAvgSal AS ( Se l e c t E. di d, Avg( E. s a l ) a s avgs al Fr

m

Em p E Gr

up

ByE. di d) Se l e c tE. e i d, E. s al Fr

m Em p

E, De pt D, De pAvgSal V W he r eE. di d=D. di d AND D. di d=V. di d And E. age < 30 a nd D. budge t > 100k And E. s al > V. avgs al

SLIDE 8

8 Re wr i t e s :SI Ps

Se l e c tE. e i d, E. s a l Fr

m Em p

E, De pt D, De pAvgSa l V W he r eE. di d=D. di d AND D. di d=V. di d And E. a ge < 30 a nd D. budge t > 100k And E. s a l > V. a vgs a l

De

pAvgs a l ne e ds t

be

e va l ua t e d

nl

y f

r

de pa r t m e nt s whe r e V. di d I N Se l e c tE. di d Fr

m

Em p E, De pt D W he r e E. di d=D. di d And E. a ge < 30 a nd D. budge t > 100K

Suppor t i ng Vi e ws

1. Cr e a t e Vi e w Pa r t i a l Re s ul t a s ( Se l e c t E. e i d, E. s a l , E. di d Fr

m

Em p E, De pt D W he r e E. di d=D. di d And E. a ge < 30 a nd D. budge t > 100K) 2. Cr e a t e Vi e w Fi l t e r AS Se l e c t DI STI NCT P. di d FROM Pa r t i a l Re s ul t P. 2. Cr e a t e Vi e w Li m i t e dAvgSa l a s ( Se l e c t F. di d Avg( E. Sa l ) a s a vgSa l Fr

m

Em p E, Fi l t e r F W he r e E. di d=F. di d Gr

up

By F. di d)

And Fi na l l y…

Tr a ns f

r

m e d que r y: Se l e c t P. e i d, P. s a l Fr

m

Pa r t i a l Re s ul t P, Li m i t e dAvgSa l V W he r eP. di d=V. di d And P. s a l > V. a vgs a l

Re wr i t e s : Gr

up

By a nd J

i

n

Sc

he m a :

– Pr

duc

t ( pi d, uni t pr i ce , … ) – Sa l e s ( t i d, da t e , s t

r

e , pi d, uni t s )

Tr

e e s :

J

i

n gr

upBy(

pi d) Sum ( uni t s ) Sc a n( Sa l e s ) Fi l t e r ( da t e i n Q2, 2000) Pr

duc

t s Fi l t e r ( i n NW ) J

i

n gr

upBy(

pi d) Sum ( uni t s ) Sc a n( Sa l e s ) Fi l t e r ( da t e i n Q2, 2000) Pr

duc

t s Fi l t e r ( i n NW )

Re wr i t e s : Ope r a t i

n

I nt r

duc

t i

n
Sc

he m a :

( pi d de t e r m i ne s c i d) – Ca t e gor y ( pi d, c i d, de t a i l s ) – Sa l e s ( t i d, da t e , s t

r

e , pi d, am ount )

Tr

e e s :

J

i

n gr

upBy(

c i d) Sum ( a m ount ) Sc a n( Sa l e s ) Fi l t e r ( s t

r

e I N {CA, W A}) Ca t e gor y Fi l t e r ( … ) J

i

n gr

upBy(

c i d) Sum ( a m ount ) Sc a n( Sa l e s ) Fi l t e r ( s t

r

e I N {CA, W A}) Ca t e gor y Fi l t e r ( … ) gr

upBy(

pi d) Sum ( am ount )

Sc he m a f

r

Som e Exa m pl e s

Re

s e r ve s :

– Ea c h t upl e i s 40 byt e s l

ng,

100 t upl e s pe r page , 1000 page s ( 4000 t upl e s )

Sa

i l

r

s :

– Ea c h t upl e i s 50 byt e s l

ng,

80 t upl e s pe r page , 500 page s ( 4000 t upl e s ) .

Sailors (sid: integer, sname: string, rating: integer, age: real) Reserves (sid: integer, bid: integer, day: dates, rname: string)

SLIDE 9

9 Que r y Re wr i t i ng: Pr e di c a t e Pus hdown

Reser ves Sai l

r

s

si d=si d bi d=100 r at i ng > 5 snam e

Reser ves Sai l

r

s

si d=si d bi d=100 snam e r at i ng > 5

( Scan; w r i t e t

t

em p T1) ( Scan; w r i t e t

t

em p T2)

The e a r l i e r we pr

c

e s s s e l e c t i

ns

, l e s s t upl e s we ne e d t

m a

ni pul a t e hi ghe r up i n t he t r e e . Di s a dva nt a ge s ?

Que r y Re wr i t e s : Pr e di c a t e Pus hdown ( t hr

ugh

gr

upi

ng)

Se l e c t bi d, M a x( a ge ) Fr

m

Re s e r ve s R, Sa i l

r

s S W he r e R. s i d=S. s i d Gr

upBy

bi d Ha vi ng M a x( a ge ) > 40 Se l e c t bi d, M a x( a ge ) Fr

m

Re s e r ve s R, Sa i l

r

s S W he r e R. s i d=S. s i d a nd S. a ge > 40 Gr

upBy

bi d

For

e ac h boat , f i nd t he max i mal age

f

s ai l

r

s who’ v e r e s e r v e d i t .

Adva

nt a ge : t he s i z e

f

t he j

i

n wi l l be s m a l l e r .

Re

qui r e s t r a ns f

r

m a t i

n

r ul e s s pe c i f i c t

t

he gr

upi

ng/ a ggr e ga t i

n
pe

r a t

r

s .

W i

l l i t wor k work i f we r e pl ac e M ax by M i n?

Que r y Re wr i t e : Pr e di c a t e M ove a r

und

Cr e a t e Vi e w V1 AS Se l e c t r a t i ng, M i n( a ge ) Fr

m

Sa i l

r

s S W he r e S. a ge < 20 Gr

up

By r a t i ng Cr e a t e Vi e w V2 AS Se l e c t s i d, r a t i ng, a ge , da t e Fr

m

Sa i l

r

s S, Re s e r ve s R W he r e R. s i d=S. s i d Se l e c t s i d, da t e Fr

m

V1, V2 W he r e V1. r a t i ng = V2. r a t i ng a nd V1. a ge = V2. a ge Sa i l i ng wi z da t e s : whe n di d t he younge s t

f

e a c h s a i l

r

l e vel r e nt boa t s ?

Que r y Re wr i t e : Pr e di c a t e M ove a r

und

Cr e a t e Vi e w V1 AS Se l e c t r a t i ng, M i n( a ge ) Fr

m

Sa i l

r

s S W he r e S. a ge < 20 Gr

up

By r a t i ng Cr e a t e Vi e w V2 AS Se l e c t s i d, r a t i ng, a ge , da t e Fr

m

Sa i l

r

s S, Re s e r ve s R W he r e R. s i d=S. s i d Se l e c t s i d, da t e Fr

m

V1, V2 W he r e V1. r a t i ng = V2. r a t i ng a nd V1. a ge = V2. a ge , a ge < 20 Sa i l i ng wi z da t e s : whe n di d t he younge s t

f

e a c h s a i l

r

l e vel r e nt boa t s ? Fi r s t , mov e pr e di c at e s up t he t r e e .

Que r y Re wr i t e : Pr e di c a t e M ove a r

und

Cr e a t e Vi e w V1 AS Se l e c t r a t i ng, M i n( a ge ) Fr

m

Sa i l

r

s S W he r e S. a ge < 20 Gr

up

By r a t i ng Cr e a t e Vi e w V2 AS Se l e c t s i d, r a t i ng, a ge , da t e Fr

m

Sa i l

r

s S, Re s e r ve s R W he r e R. s i d=S. s i d, a nd S. a ge < 20. Se l e c t s i d, da t e Fr

m

V1, V2 W he r e V1. r a t i ng = V2. r a t i ng a nd V1. a ge = V2. a ge , a nd a ge < 20 Sa i l i ng wi z da t e s : whe n di d t he younge s t

f

e a c h s a i l

r

l e vel r e nt boa t s ? Fi r s t , mov e pr e di c at e s up t he t r e e . The n, mov e t he m down.

Que r y Re wr i t e Sum m ar y

The
pt

i m i z e r c a n us e a ny s e mant i c al l y c

r

r e c tr ul e t

t

r a ns f

r

m

ne

que r y t

a

not he r .

Rul

e s t r y t

:

– m ove c

ns

t r a i nt s be t we e n bl

c

ks ( be c a us e e a c h wi l l be

pt

i m i z e d s e pa r a t e l y) – Unne s t bl

c

ks

Es

pe c i a l l y i m por t a nt i n de c i s i

n

s uppor t a ppl i c a t i

ns

whe r e que r i e s a r e ve r y c

m pl

e x.

I

n a f e w m i nut e s

f

t hought , you’ l l c

m e

up wi t h your

wn

r e wr i t e . Som e que r y, s

m e

whe r e , wi l l be ne f i t f r

m

i t .

The
r

e m s ?

SLIDE 10

10 Cos t Es t i m a t i

n
For

e a c h pl a n c

ns

i de r e d, m us t e s t i m a t e c

s

t :

– M us t e s t i m a t e cos tof e a c h

pe

r a t i

n

i n pl a n t r e e .

De

pe nds

n

i nput c a r di na l i t i e s .

– M us t e s t i m a t e s i z e

f

r e s ul t f

r

e a ch

pe

r a t i

n

i n t r e e !

Us

e i nf

r

m a t i

n

a bout t he i nput r e l a t i

ns

.

For

s e l e c t i

ns

a nd j

i

ns , a s s um e i nde pe nde nc e

f

pr e di c a t e s .

W e

’ l l di s c us s t he Sys t e m R cos t e s t i m a t i

n

a ppr

a

c h.

– Ve r y i ne xa c t , but wor ks

k

i n pr a c t i c e . – M or e s

phi

s t i c a t e d t e c hni que s known now.

St a t i s t i c s a nd Ca t a l

gs
Ne

e d i nf

r

m a t i

n

a bout t he r e l a t i

ns

a nd i nde xe s i nvol ve d. Cat al

gst

ypi c al l y cont a i n a t l e a s t :

– # t upl e s ( NTupl e s ) a nd # pa ge s ( NPa ge s ) f

r

e a c h r e l a t i

n.

– # di s t i nc t ke y val ue s ( NKe ys ) a ndNPa ge s f

r

e a c h i nde x. – I nde x he i ght , l

w/

hi gh ke y val ue s ( Low/ Hi gh) f

r

e a c h t r e e i nde x.

Ca

t al

gs

upda t e d pe r i

di

cal l y.

– Upda t i ng whe ne ve r da t a c ha nge s i s t

e

xpe ns i ve ; l

t

s

f

a ppr

xi

m a t i

n

a nywa y, s

s

l i ght i nc

ns

i s t e nc y

k.
M or

e de t a i l e d i nf

r

m a t i

n

( e . g. , hi s t

gr

am s

f

t he val ue s i n s

m e

f i e l d) a r e s

m e

t i m e s s t

r

e d.

Si z e Es t i m a t i

n

a nd Re duc t i

n

Fa c t

r

s

Cons

i de r a que r y bl

c

k:

M a

xi m um # t upl e s i n r e s ul t i s t he pr

duc

t

f

t he c a r di na l i t i e s

f

r e l a t i

ns

i n t he FROM c l a us e .

Re

duc t i

n

f ac t

r

( RF) a s s

c

i a t e d wi t h e a c ht e r m r e f l e c t s t he i m pa c t

f

t he t e r m i n r e duc i ng r e s ul t s i z e . Re s ul tc ar di nal i t y= M a x # t upl e s * pr

duc

t

f

a l lRF’ s .

– I m pl i c i t a s s um pt i

n t

ha t t e r msa r e i nde pe nde nt ! – Te r m c

l

=v al ue ha s RF 1/ NKe y s ( I ) , gi ve n i nde x I

n c
l

– Te r m c

l

1=c

l

2 ha s RF 1/ M AX( NKe y s ( I 1) ,NKe y s ( I 2) ) – Te r m c

l

>v al ue ha s RF ( Hi gh( I )

v

al ue ) / ( Hi gh( I )

Low(

I ) ) SELECT attribute list FROM relation list WHERE term1 AND ... AND termk

Hi s t

gr

a m s

Key

t

bt

a i ni ng good c

s

t a nd s i z e e s t i m a t e s .

Com e

i n s e ve r a l f l a vor s :

– Equi

de

pt h – Equi

wi

dt h

W hi

c h i s be t t e r ?

Com pr

es s e d hi s t

gr

am s : s pe c i a l t r e a t m e nt

f

f r e que nt va l ue s .

Hi s t

gr

a m s

St

a t i s t i c s

n

da t a m a i nt a i ne d by t he RDBM S

M ake

s s i z e e s t i m a t i

n

m uc h m or e a c c ur a t e ( he nc e , c

s

t e s t i m a t i

ns

a r e m or e a c c ur a t e )

Hi s t

gr

a m s

Em pl

ye

e ( s s n, na m e , s a l a r y, phone )

M a

i nt a i n a hi s t

gr

a m

n

s a l a r y:

T(

Em pl

ye

e ) = 25000, but now we know t he di s t r i but i

n

500 > 100k 6500 80k. . 100k 12000 5000 800 200 Tupl e s 60k. . 80k 40k. . 60k 20k. . 40k 0. . 20k Sal ar y:

SLIDE 11

11 Hi s t

gr

a m s

Ra nks ( r a nkNam e , s a l a r y)

Es

t i m a t e t he s i z e

f

Em pl

ye

e Ra nks

500 > 100k 6500 80k. . 100k 12000 5000 800 200 60k. . 80k 40k. . 60k 20k. . 40k 0. . 20k Em pl

yee

2 > 100k 100 80k. . 100k 80 40 20 8 60k. . 80k 40k. . 60k 20k. . 40k 0. . 20k Ra nks Sa l a r y

Hi s t

gr

a m s

As

s um e :

– V( Em pl

ye

e , Sal a r y) = 200 – V( Ra nks , Sa l a r y) = 250

The

n T( Em pl

ye

e Ra nks ) = = Si

=1, 6 TiTi

’ / 250 = ( 200x8 + 800x20 + 5000x40 + 12000x80 + 6500x100 + 500x2) / 250 = … .

Sa l a r y

Pl a ns f

r

Si ngl e

Re

l a t i

n

Que r i e s ( Pr e p f

r

J

i

n

r

de r i ng)

Tas

k:c r e a t e a que r y e xe c ut i

n

pl a n f

r

a s i ngl e Se l e c t

pr
j

e c t

gr
up-

by bl

c

k.

K e

y i de a:c

ns

i de r e a c h pos s i bl e ac c e s s pat h t

t

he r e l e va nt t upl e s

f

t he r e l a t i

n.

Choos e t he c he a pe s t

ne

.

The

di f f e r e nt

pe

r a t i

ns

a r e e s s e nt i a l l y c a r r i e d

ut

t

ge

t he r ( e . g. , i f a n i nde x i s us e d f

r

a s e l e c t i

n,

pr

j

e c t i

n

i s done f

r

e a c h r e t r i e ve d t upl e , a nd t he r e s ul t i ng t upl e s a r e pi pe l i ne d i nt

t

he a ggr ega t e c

m put

a t i

n)

.

Exa m pl e

I

f we ha ve a n I nde x

n

r at i ng:

– ( 1/ NKe ys ( I ) ) *NTupl e s ( R) = ( 1/ 10) * 40000 t upl e s r e t r i e ve d. – Cl us t e r e d i nde x: ( 1/ NKe ys ( I ) ) * ( NPa ge s ( I ) +NPa ge s ( R) ) = ( 1/ 10) * ( 50+500) pa ge s a r e r e t r i e ve d ( = 55) . – Unc l us t e r e d i nde x: ( 1/ NKeys ( I ) ) * ( NPa ge s ( I ) +NTupl e s ( R) ) = ( 1/ 10) * ( 50+40000) pa ge s a r e r e t r i e ve d.

I

f we ha ve a n i nde x

ns

i d:

– W oul d ha ve t

r

e t r i e ve a l l t upl e s / pa ge s . W i t h a c l us t e r e d i nde x, t he c

s

t i s 50+500.

Doi

ng a f i l e s c a n: we r e t r i e ve a l l f i l e pa ge s( 500) .

SELECT S.sid FROM Sailors S WHERE S.rating=8

De t e r m i ni ng J

i

n Or de r i ng

R1

R2 … . Rn

J
i

n t r e e :

A

j

i

n t r e e r e pr e s e nt s a pl a n. An

pt

i m i z e r ne e ds t

i

ns pe c t m a ny ( a l l ? ) j

i

n t r e e s R3 R1 R2 R4

Type s

f

J

i

n Tr e e s

Le

f t de e p:

R3 R1 R5 R2 R4

SLIDE 12

12 Type s

f

J

i

n Tr e e s

Bus

hy:

R3 R1 R2 R4 R5

Type s

f

J

i

n Tr e e s

Ri

ght de e p:

R3 R1 R5 R2 R4

Pr

bl

e m

Gi

ve n: a que r y R1 R2 … Rn

As

s um e we ha ve a f unc t i

n

c

s

t ( ) t ha t gi ves us t he c

s

t

f

eve r y j

i

n t r e e

Fi

nd t he be s t j

i

n t r e e f

r

t he que r y

Dynam i c Pr

gr

a m m i ng

I

de a : f

r

e a c h s ubs e t

f

{R1, … , Rn}, c

m put

e t he be s t pl a n f

r

t ha t s ubs e t

I

n i nc r e a s i ng

r

de r

f

s e t c a r di na l i t y:

– St e p 1: f

r

{R1}, {R2}, … , {Rn} – St e p 2: f

r

{R1, R2}, {R1, R3}, … , {Rn- 1, Rn} – … – St e p n: f

r

{R1, … , Rn}

A

s ubs e t

f

{R1, … , Rn} i s a l s

c

a l l e d a s ubque r y

Dynam i c Pr

gr

a m m i ng

For

e a c h s ubque r y Q { R1, … , Rn} c

m put

e t he f

l

l

wi

ng:

– Si ze ( Q) – A be s t pl a n f

r

Q: Pl a n( Q) – The cos t

f

t ha t pl a n: Cos t ( Q)

Dynam i c Pr

gr

a m m i ng

St

e p 1: For e a c h {Ri } do:

– Si ze ( {Ri }) = B( Ri ) – Pl a n( {Ri }) = Ri – Cos t ( {Ri }) = ( cos t

f

s ca nni ng Ri )

SLIDE 13

13 Dynam i c Pr

gr

a m m i ng

St

e p i : For e a c h Q { R1, … ,Rn}

f

c a r di na l i t y i do:

– Com put e Si ze ( Q) ( l a t e r … ) – For e ve r y pa i r

f

s ubque r i e s Q’ , Q’ ’ s . t . Q = Q’ U Q’ ’ c

m put

e cos t ( Pl a n( Q’ ) Pl an( Q’ ’ ) ) – Cos t ( Q) = t he s m al l e s t s uc h cos t – Pl a n( Q) = t he cor r e s pondi ng pl a n

Dynam i c Pr

gr

a m m i ng

Re

t ur n Pl a n( {R1, … , Rn})

Dynam i c Pr

gr

a m m i ng

Sum m a

r y: c

m put

e s

pt

i m a l pl a ns f

r

s ubque r i e s :

– St ep 1: {R1}, {R2}, … , {Rn} – St ep 2: {R1, R2}, {R1, R3}, … , {Rn- 1, Rn} – … – St ep n: {R1, … , Rn}

W e

us e d na ï ve s i z e / c

s

t e s t i m a t i

ns
I

n pr a c t i c e :

– mor e r e al i s t i c s i ze / cos t e s t i mat i

ns

( ne xt ) – heur i s t i c s f

r

Reduci ng t he Se ar ch Spac e

Re

s t r i c t t

l

ef t l i ne a r t r e e s

Re

s t r i c t t

t

r e es “wi t hout c a r t e s i a n pr

duc

t ”

– ne ed m or e t han j us t

ne

pl an f

r

e ach s ubque r y:

“i

nt e r e s t i ng

r

de r s ”