cs 4604: introduction to database management...

CS4604:IntroductiontoDatabaseManagementSystems

B.AdityaPrakashLecture#10:QueryProcessing

Prakash2018 VTCS4604 2

Outline

§  introduction§  selection§  projection§  join§  set&aggregateoperations


Introduction

§  Today’stopic:QUERYPROCESSING§  SomedatabaseoperationsareEXPENSIVE§  Cangreatlyimproveperformancebybeing“smart”–  e.g.,canspeedup1,000,000xovernaïveapproach


Introduction(cnt’d)

§ Mainweaponsare:– cleverimplementationtechniquesforoperators– exploiting“equivalencies”ofrelationaloperators– usingstatisticsandcostmodelstochooseamongthese.


AReallyBadQueryOptimizer

§  ForeachSelect-From-Wherequeryblock– docartesianproductsfirst–  thendoselections–  etc,ie.:

•  GROUPBY;HAVING•  projections•  ORDERBY

§  Incrediblyinefficient– Hugeintermediateresults!

× σpredicates

tables …


Cost-basedQuerySub-System

Query Parser

Query Optimizer

Plan Generator Plan Cost Estimator

Query Plan Evaluator

Catalog Manager

Usuallythereisaheuristics-basedrewritingstepbeforethecost-basedsteps.

Schema Statistics

Select * From Blah B Where B.blah = blah

Queries


TheQueryOptimizationGame§  “Optimizer”isabitofamisnomer…§  Goalistopicka“good”(i.e.,lowexpectedcost)plan.–  Involveschoosingaccessmethods,physicaloperators,operatororders,…

– Notionofcostisbasedonanabstract“costmodel”


RelationalOperations§  Wewillconsiderhowtoimplement:–  Selection(σ)Selectsasubsetofrowsfromrelation.–  Projection(π)Deletesunwantedcolumnsfromrelation.–  Join()Allowsustocombinetworelations.–  Set-difference(-)Tuplesinreln.1,butnotinreln.2.–  Union(∪)Tuplesinreln.1andinreln.2.–  Aggregation(SUM,MIN,etc.)andGROUPBY

§  Recall:opscanbecomposed!§  Later(afterspringbreak),we’llseehowtooptimizequerieswithmanyops

▹◃


SchemaforExamples

§  Similartooldschema;rnameaddedforvariations.§  Sailors:–  Eachtupleis50byteslong,80tuplesperpage,500pages.–  N=500,pS=80.

§  Reserves:–  Eachtupleis40byteslong,100tuplesperpage,1000pages.– M=1000,pR=100.

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


SimpleSelections

§  Oftheform§  Question:howbesttoperform?

SELECT*FROMReservesRWHERER.rname<‘C%’

σ R attr valueop R. ( )


SimpleSelections

§  A:Dependson:– whatindexes/accesspathsareavailable– whatistheexpectedsizeoftheresult(intermsofnumberoftuplesand/ornumberofpages)


SimpleSelections

§  Sizeofresultapproximatedas sizeofR*reductionfactor– “reductionfactor”isalsocalledselectivity.– estimateofreductionfactorsisbasedonstatistics–wewilldiscussshortly.


AlternativesforSimpleSelections

§ Withnoindex,unsorted:– Mustessentiallyscanthewholerelation– costisM(#pagesinR).For“reserves”=1000I/Os.


SimpleSelections(cnt’d)

§ Withnoindex,sorted:– costofbinarysearch+numberofpagescontainingresults.

– Forreserves=10I/Os+⎡selectivity*#pages⎤


SimpleSelections(cnt’d)

§ Withanindexonselectionattribute:– Useindextofindqualifyingdataentries,–  thenretrievecorrespondingdatarecords.–  (Hashindexusefulonlyforequalityselections.)


UsinganIndexforSelections

§  Costdependson#qualifyingtuples,andclustering.– Cost:•  findingqualifyingdataentries(typicallysmall)• pluscostofretrievingrecords(couldbelargew/oclustering).


SelectionsusingIndex(cnt’d)

Index entries

Data entries

direct search for

(Index File) (Data file)

Data Records

data entries

Data entries

Data Records

CLUSTEREDUNCLUSTERED


SelectionsusingIndex(cnt’d)–  Inexample“reserves”relation,if10%oftuplesqualify(100pages,10,000tuples).• Withaclusteredindex,costislittlemorethan100I/Os;•  ifunclustered,couldbeupto10,000I/Os!unless…


SelectionsusingIndex(cnt’d)§  Importantrefinementforunclusteredindexes:

1.Findqualifyingdataentries.2.Sorttherid’softhedatarecordstoberetrieved.3.Fetchridsinorder.Thisensuresthateachdatapageislookedatjustonce(though#ofsuchpageslikelytobehigherthanwithclustering).


GeneralSelectionConditions

§  Q:Whatwouldyoudo?

(day<8/9/94 AND rname=‘Paul’) OR bid=5 OR sid=3 SKIP



§  Q:Whatwouldyoudo?§  A:trytofindaselective(clustering)index.Specifically:




§  Converttoconjunctivenormalform(CNF):–  (day<8/9/94ORbid=5ORsid=3)AND(rname=‘Paul’ ORbid=5ORsid=3)

§  WeonlydiscussthecasewithnoORs(aconjunctionoftermsoftheformattropvalue).




§  AB-treeindexmatches(aconjunctionof)termsthatinvolveonlyattributesinaprefixofthesearchkey.–  Indexon<a,b,c>matchesa=5ANDb=3,butnotb=3.

§  ForHashindex,musthaveallattributesinsearchkey



TwoApproachestoGeneralSelections

§  Firstapproach:Findthecheapestaccesspath,retrievetuplesusingit,andapplyanyremainingtermsthatdon’tmatchtheindex

§  Secondapproach:getridsfromfirstindex;ridsfromsecondindex;intersectandfetch.

SKIP


TwoApproachestoGeneralSelections

§  Firstapproach:Findthecheapestaccesspath,retrievetuplesusingit,andapplyanyremainingtermsthatdon’tmatchtheindex:– Cheapestaccesspath:AnindexorfilescanwithfewestI/Os.

– Termsthatmatchthisindexreducethenumberoftuplesretrieved;othertermshelpdiscardsomeretrievedtuples,butdonotaffectnumberoftuples/pagesfetched.

SKIP


CheapestAccessPath-Example§  Considerday<8/9/94ANDbid=5ANDsid=3.

§  AB+treeindexondaycanbeused;– then,bid=5andsid=3mustbecheckedforeachretrievedtuple.

§  Similarly,ahashindexon<bid,sid>couldbeused;– Then,day<8/9/94mustbechecked.

SKIP


CheapestAccessPath-cnt’d

§  Considerday<8/9/94ANDbid=5ANDsid=3.

§  HowaboutaB+treeon<rname,day>?§  HowaboutaB+treeon<day,rname>?§  HowaboutaHashindexon<day,rname>?

SKIP


IntersectionofRIDs

§  Secondapproach:ifwehave2ormorematchingindexes(w/Alternatives(2)or(3)fordataentries):– Getsetsofridsofdatarecordsusingeachmatchingindex.

– Thenintersectthesesetsofrids.– Retrievetherecordsandapplyanyremainingterms.

SKIP


IntersectionofRIDs(cnt’d)

§  EXAMPLE:Considerday<8/9/94ANDbid=5ANDsid=3.

§  WithaB+treeindexondayandanindexonsid,§  wecanretrieveridsofrecordssatisfyingday<8/9/94usingthefirst,

§  ridsofrecssatisfyingsid=3usingthesecond,§  intersect,§  retrieverecordsandcheckbid=5.

SKIP


TheProjectionOperation

§  Issueisremovingduplicates.§  Basicapproach:sorting– 1.ScanR,extractonlytheneededattrs(why?)– 2.Sorttheresultingset– 3.RemoveadjacentduplicatesCost:Reserveswithsizeratio0.25=250pages.With20bufferpagescansortin2passes,so1000+250+2*2*250+250=2500I/Os

SELECTDISTINCTR.sid,R.bidFROMReservesR


Projection

§  Canimprovebymodifyingexternalsortalgorithm(seechapter13):– ModifyPass0ofexternalsorttoeliminateunwantedfields.

– Modifymergingpassestoeliminateduplicates.Cost:forabovecase:read1000pages,writeout250inrunsof40pages,mergeruns=1000+250+250=1500.

SKIP


DiscussionofProjection

§  Ifanindexontherelationcontainsallwantedattributesinitssearchkey,candoindex-onlyscan.– Applyprojectiontechniquestodataentries(muchsmaller!)


DiscussionofProjection

§  Ifanordered(i.e.,tree)indexcontainsallwantedattributesasprefixofsearchkey,candoevenbetter:– Retrievedataentriesinorder(index-onlyscan),discardunwantedfields,compareadjacenttuplestocheckforduplicates.

AB-treeindexmatches(aconjunctionof)termsthatinvolveonlyattributesinaprefixofthesearchkey.–  Indexon<a,b,c>matchesa=5ANDb=3,butnotb=3.

ForHashindex,musthaveallattributesinsearchkey


Joins

§  Joinsareverycommon.§  Joinscanbeveryexpensive(crossproductin

worstcase).§  Manyapproachestoreducejoincost.


Joins

§  Jointechniqueswewillcover:– Nested-loopsjoin–  Index-nestedloopsjoin– Sort-mergejoin– Hashjoin


EqualityJoinsWithOneJoinColumn

§  Inalgebra:RS.Common!Mustbecarefullyoptimized.R×Sislarge;so,R×Sfollowedbyaselectionisinefficient.

§  Remember,joinisassociativeandcommutative.

SELECT*FROMReservesR1,SailorsS1WHERER1.sid=S1.sid

▹◃


EqualityJoins

§  Assume:– MpagesinR,pRtuplesperpage,mtuplestotal– NpagesinS,pStuplesperpage,ntuplestotal–  Inourexamples,RisReservesandSisSailors.

§ Wewillconsidermorecomplexjoinconditionslater.

§  Costmetric:#ofI/Os.Wewillignoreoutputcosts.


Nestedloops

§  Algorithm#0:(naive)nestedloop(SLOW!)

R(A,..)

S(A, ......) m

n


Nestedloops

§  Algorithm#0:(naive)nestedloop(SLOW!)foreachtuplerofR

foreachtuplesofSprint,iftheymatch

R(A,..)

S(A, ......) m

n


Nestedloops

§  Algorithm#0:(naive)nestedloop(SLOW!)foreachtuplerofR

foreachtuplesofSprint,iftheymatch

R(A,..)

S(A, ......) m

n

outer relation

inner relation


Nestedloops

§  Algorithm#0:whyisitbad?§  howmanydiskaccesses(‘M’and‘N’arethenumberofblocksfor‘R’and‘S’)?

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples


Nestedloops

§  Algorithm#0:whyisitbad?§  howmanydiskaccesses(‘M’and‘N’arethenumberofblocksfor‘R’and‘S’)?M+m*N

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples


SimpleNestedLoopsJoin

§  Actualnumber(pR*M)*N+M=100*1000*500+1000I/Os.– At10ms/IO,Total:???

§ Whatifsmallerrelation(S)wasouter?

§ Whatassumptionsarebeingmadehere?


SimpleNestedLoopsJoin

§  Actualnumber§  (pR*M)*N+M=100*1000*500+1000I/Os.– At10ms/IO,Total:~6days(!)

§ Whatifsmallerrelation(S)wasouter?– slightlybetter

§ Whatassumptionsarebeingmadehere?– 1bufferforeachtable(and1foroutput)


Nestedloops

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  Algorithm #1: Blocked nested-loop join –  read in a block of R

•  read in a block of S –  print matching tuples COST?


Nestedloops

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  Algorithm #1: Blocked nested-loop join –  read in a block of R

•  read in a block of S –  print matching tuples COST= M+M*N


Nestedloops

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  Which one should be the outer relation?

COST= M+M*N


Nestedloops

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  Which one should be the outer relation? •  A: the smallest (page-wise)

COST= M+M*N


Nestedloops

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  M=1000, N=500 •  Cost = 1000 + 1000*500 = 501,000 •  = 5010 sec ~ 1.4h COST= M+M*N


Nestedloops

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  M=1000, N=500 - if smaller is outer: •  Cost = 500 + 1000*500 = 500,500 •  = 5005 sec ~ 1.4h COST= N+M*N


Nestedloops

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  What if we have B buffers available?


Nestedloops

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  What if we have B buffers available? •  A: give B-2 buffers to outer, 1 to inner, 1 for

output


Nestedloops

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  Algorithm #1: Blocked nested-loop join –  read in B-2 blocks of R

•  read in a block of S –  print matching tuples COST= ?


Nestedloops

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  Algorithm #1: Blocked nested-loop join –  read in B-2 blocks of R

•  read in a block of S –  print matching tuples COST= M+M/(B-2)*N


Nestedloops

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  and, actually: •  Cost = M + ceiling(M/(B-2)) * N

COST= M+M/(B-2)*N


Nestedloops

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  If smallest (outer) fits in memory •  (ie., B= N+2), •  Cost =? COST= N+N/(B-2)*M


Nestedloops

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  If smallest (outer) fits in memory •  (ie., B= N+2), •  Cost =N+M (minimum!) COST= N+N/(B-2)*M


Nestedloops-guidelines

§  pickasouterthesmallesttable(=fewestpages)

§  fitasmuchofitinmemoryaspossible§  loopovertheinner


§  useanexistingindex,orevenbuildoneonthefly

§  cost:M+m*c(c:look-upcost)

IndexNLjoin

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples


§  cost:M+m*c(c:look-upcost)§  ‘c’dependswhethertheindexisclusteredornot.

IndexNLjoin

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples


Joins



Sort-mergejoin

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  sort both on joining attributed •  scan each and merge •  Cost, given B buffers?


Sort-mergejoin

R(A,..)

S(A, ......) M pages,

m tuples N pages,

n tuples

•  Cost, given B buffers? •  ~ 2*M*logM/logB + 2*N* logN/logB + M + N


Sort-MergeJoin§  Usefulif


Sort-MergeJoin§  Usefulif– oneorbothinputsarealreadysortedonjoinattribute(s)

– outputisrequiredtobesortedonjoinattributes(s)

§  “Merge”phasecanrequiresomebacktrackingifduplicatevaluesappearinjoincolumn


ExampleofSort-MergeJoin

sid sname rating age22 dustin 7 45.028 yuppy 9 35.031 lubber 8 55.544 guppy 5 35.058 rusty 10 35.0

sid bid day rname28 103 12/4/96 guppy28 103 11/3/96 yuppy31 101 10/10/96 dustin31 102 10/12/96 lubber31 101 10/11/96 lubber58 103 11/12/96 dustin


ExampleofSort-MergeJoin

§  With35,100or300bufferpages,bothReservesandSailorscanbesortedin2passes;totaljoincost:7500.

§  (while Block Nested Loop (BNL) cost: 2,500 to 15,000 I/Os)


Sort-mergejoin

§ Worstcaseformergingphase?

§  Cost?


Refinements

§  Alltherefinementsofexternalsorting§  plusoverlappingofthemergingofsortingwiththemergingofjoining.


Joins



§  hashjoin:usehashingfunctionh()– hash‘R’into(0,1,...,‘max’)buckets– hash‘S’intobuckets(samehashfunction)–  joineachpairofmatchingbuckets

Hashjoins

R(A, ...) S(A, ......) 0

1

max


– howtojoineachpairofpartitionsHr-i,Hs-i?– A:buildanotherhashtableforHs-i,andprobeitwitheachtupleofHr-i

Hashjoin-details

R(A, ...) S(A, ......)

Hr-0

0

1

max

Hs-0


Hashjoin-details

§  Inmoredetail:§  Choosethe(page-wise)smallest-ifitfitsinmemory,do~NL– and,actually,buildahashtable(withh2()!=h())– andprobeit,witheachtupleoftheother


§  whatifHs-iistoolargetofitinmain-memory?

§  A:recursivepartitioning§  moredetails(overflows,hybridhashjoins):inbook

§  costofhashjoin?(ifwehaveenoughbuffers:)3(M+N)(why?Seenextslide)

Hashjoindetails


CostofHash-Join

§  Inpartitioningphase,read+writebothrelns;2(M+N).Inmatchingphase,readbothrelns;M+NI/Os.

§  Inourrunningexample,thisisatotalof4500I/Os.


§  [costofhashjoin?(ifwehaveenoughbuffers:)3(M+N)]

§ Whatis‘enough’?sqrt(N),orsqrt(M)?

Hashjoindetails


§  [costofhashjoin?(ifwehaveenoughbuffers:)3(M+N)]

§  Whatis‘enough’?sqrt(N),orsqrt(M)?§  A:sqrt(smallest)(why?)–  BecauseyouonlyneedenoughmemorytoholdthehashtablepartitionsofthesmallertableinmemorysoB>sizeofsmaller/B-1èB~sqrt(size-of-smaller)

Hashjoindetails Details


Sort-MergeJoinvs.HashJoin

§  Givenaminimumamountofmemorybothhaveacostof3(M+N)I/Os.

(min.memoryforsort-merge=sqrt(largertable)usingaggressiverefinements---intextbook)(min.memoryforhash=sqrt(smallertable)---seepreviousslides)


Sort-MergevsHashjoin

§  HashJoinPros:– ??– ??– ??

§  Sort-MergeJoinPros:– ??



§  HashJoinPros:– Superiorifrelationsizesdiffergreatly– Showntobehighlyparallelizable(beyondscopeofclass)

§  Sort-MergeJoinPros:– ??



§  HashJoinPros:– Superiorifrelationsizesdiffergreatly– Showntobehighlyparallelizable(beyondscopeofclass)

§  Sort-MergeJoinPros:– Lesssensitivetodataskew– Resultissorted(mayhelp“upstream”operators)– goesfasterifoneorbothinputsalreadysorted


GeneralJoinConditions

§  Equalitiesoverseveralattributes(e.g.,R.sid=S.sidANDR.rname=S.sname):– allpreviousmethodsapply,usingthecompositekey



§  Inequalityconditions(e.g.,R.rname<S.sname):§  whichmethodsstillapply?– NL–  indexNL– Sortmerge– Hashjoin



§  Inequalityconditions(e.g.,R.rname<S.sname):§  whichmethodsstillapply?– NL (probably,thebest!)–  indexNL (onlyifclusteredindex)– Sortmerge (doesnotapply!)(why?)– Hashjoin (doesnotapply!)(why?)


SetOperations

§  Intersectionandcross-product:specialcasesofjoin

§  Union(Distinct)andExcept:similar;we’lldounion:

§  Effectively:concatenate;usesortingorhashing§  Sortingbasedapproachtounion:– Sortbothrelations(oncombinationofallattributes).– Scansortedrelationsandmergethem.– Alternative:MergerunsfromPass0forbothrelations.

SKIP


SetOperations,cont’d

§  Hashbasedapproachtounion:– PartitionRandSusinghashfunctionh.– ForeachS-partition,buildin-memoryhashtable(usingh2),scancorrespondingR-partitionandaddtuplestotablewhilediscardingduplicates.

SKIP


AggregateOperations(AVG,MIN,etc.)

§ Withoutgrouping:–  Ingeneral,requiresscanningtherelation.– GivenindexwhosesearchkeyincludesallattributesintheSELECTorWHEREclauses,candoindex-onlyscan.

SKIP


Summary§  AvirtueofrelationalDBMSs:–  queriesarecomposedofafewbasicoperators–  Theimplementationoftheseoperatorscanbecarefullytuned

–  Importanttodothis!

§  Manyalternativeimplementationtechniquesforeachoperator–  Nouniversallysuperiortechniqueformostoperators.

“it depends” [Guy Lohman (IBM)]


Summarycont’d

§  Mustconsideravailablealternativesforeachoperationinaqueryandchoosebestonebasedonsystemstatistics,etc.–  Partofthebroadertaskofoptimizingaquerycomposedofseveralops.

cs 4604: introduction to database management...

Documents