查询处理优化：物化与流水线技术综述.pdfVIP

Review:MaterializationandPipelining

•Materialization

•Generateresultsofanexpressionwhoseinputsarerelations

ortheresultsarealreadycomputed,materialize(store)iton

disk.

•OverallcostSumofcostsofindividualoperations+

costofwritingintermediateresultstodisk

•Pipelining

•Passtuptoparentoperationsimmediatelyafteran

operationisbeingexecuted

•Avoidwritingintermediateresultstodisk

Exercise1:Indexnestedloopjoinandpipelining(1/3)

•ConsidertherelationsR1(A,B,C),R2(C,D,E),andR3(E,F).

Primarykeysareunderlinedandforeignkeysinitalics.

•Assumethat:

•R1has1,000tupin100pages.

•R2has10,000tupin1,000pages.

•R3has100,000tupin10,000pages.

•GiveanefficientpipeliningstrategytocomputeR1R2R3.

Exercise1:Indexnestedloopjoinandpipelining(2/3)

•R1(A,B,C);1,000tup;100pages.

•R2(C,D,E);10,000tup;1,000pages.

•R3(E,F);100,000tup;10,000pages.

•Thesizeoftheresultis1,000tup.

•(R1R2)R3ischeaperthanR1(R2R3),asisdiscussed

duringtheclass.

Exercise1:Indexnestedloopjoinandpipelining(3/3)

•Anefficientstrategyforcomputing(R1R2)R3wouldbetocreate

•IndexonattributeCforrelationR2

•IndexonattributeEforrelationR3

•Sincetheconditionisequality(onakeyattribute),hashindexisbest.

•ThenforeachtupleinR1,wedothefollowing:

a.SearchinR2tofindthematchwiththeCvalueofR1.

Costof2:1diskaccesstofindtheentryCinthehashindex(assumingnooverflowbuckets);

1diskaccesstoretrievethetuplefromthefileofR2.

b.Then,fortheintermediatetupleof(R1R2),searchinR3tolookupat