Adapting materialized views after redefinitions: techniques and a performance study.

Full text

Page 1

AdaptingMaterializedViewsafterRede?nitions?
Tec hniquesandaP erformanceStudy
?
AshishGupta
y
IBMAlmaden ResearchCenter
ashish?almaden?ibm?com
InderpalS?Mumick
A T?TLaboratories
mumick?research?att?com
JunRao
z
ColumbiaUniversity
junr?cs?colum bia?edu
Kenneth A?Ross
z
Colum biaUniversity
k ar?cs?columbia?edu
Colum biaUniversityT echnical ReportCUCS???????
Mar? ???????
Abstract
Weconsideravariant of theviewmain tenanceproblem? How does onek eepamaterialized
viewup?to?date whentheviewde?nitionitself changes? Canonedobetter thanrecomputingthe
viewfromthe baserelations?T raditionalviewmain tenancetries tomaintain thematerialized
view inresp onseto modi?cationsto thebase relations?we tryto ?adapt?theview inresponse
toc hangesin theview de?nition?
Such techniquesareneeded forapplicationswhere theuser canchangequeries dynamically
andseethechanges intheresultsfast?Data archaeology?datavisualization?anddynamic
queriesareexamplesofsuchapplications?
Weconsider allp ossiblerede?nitions of SQLSELECT?FROM?WHERE?G RO UPBY? HAVING?UNION?
andEXCEPT views?andshow howtheseviewscanbeadaptedusingtheoldmaterializationfor
thecaseswhere itisp ossibletodo so?Weidentifyextrainformationthatcanbekeptwith
amaterializationto facilitate rede?nition? Multiple simultaneouschangestoaviewcanbe
handledwithoutnecessarily materializingintermediate results?Weiden tifyguidelinesforusers
anddatabase administratorsthat canbeusedtofacilitatee?cient view adaptation?
Weperformasystematicexperimen talev aluationof ourproposed techniques? Ourevalu?
ationindicatesthat adaptationismore e?cientthanrematerialization inmost cases? Certain
adaptation techniquescanbe up to?????timesb etter?Wealsop oint outthephysical layouts
that canbene?t adaptation?
?
Apreliminary version ofthispap erappearedas ?GMR????
y
Researchsupportedby NSFgran tsIRI????????? andIRI??????????
z
Researchsupportedbyagrant fromtheAT?TFoundation? byaDavidandLucilePackardF oundationFellowship
in ScienceandEngineering?byaSloanFoundationFello wship?by NSFgran tsIRI????????? CD A??????????andby
an NSFYoung Investigatoraward?
?

Page 2

?Introduction
Manyapplications trytovisualizeviewsov erdata stored inadatabase? Theviewismaterialized?
anda graphicaldisplay programma ypresentthedata intheviewvisually? Iftheuserc hangesthe
view de?nition?thesystemm ustbeable torecomputethe viewfastinordertok eeptheapplication
interactive? Anin terface forsuc hqueriesin a realestate system isrep ortedin?WS?? ??wherethey
arecalled dynamicqueries?AWS?? ??
Data archaeology?BST
?
??? BST
?
???is anotherapplicationwhere anarc haeologisttriesto
discov errules aboutdata byform ulatingqueries? lookingattheresultsof thequery?andthen
changingthe query iterativelyasthe archaeologist?s understandingimproves?
Weconsidertheproblem ofrecomputinga materializedviewinresp onseto changes madeto
theviewde?nition?that is?inresponsetorede?nition oftheview?Wecallthis problemthe?view
adaptationproblem??
???Motivating Example
Example ????Consider thefollo wingrelationsE?employees??W?works??andP?pro jects??
E?Emp?
?Name?Address?Age?Salary??
W?Emp?
?Proj??Hours ??
P?Pr oj?
?Projname?Leader??Location?Budget??
Thekey of eachrelation isunderlined? Consideragraphicalinterfaceusedtoposequerieson
theab overelationsusingSELECT?FROM?WHERE?GROUPBY? and otherSQLconstructs?Forinstance?
consider thefollo wingviewde?nedbyqueryQ
?
?
CREATEVIEWV AS
SELECTEmp?? Pr oj?? Salary
FROME?W
WHERE Salary??????ANDHours? ??
Thenatural joinbetweenrelationsE andW onattributeEmp? isspeci?ed asa partof the
FROM clauseusing the???sign?QueryQ
?
mightbe speci?edgraphicallyusinga slider forthe
Salaryattributeandanotherslider forthe Hoursattribute?Asthep ositionofthese slidersis
c hanged?thedisplay is up datedto re?ectthe newansw er?
Say theusershiftsthe sliderfor the Salaryattributemaking the?rst conditionSalary ? ??????
The answertothis new query canbe computedeasily fromthe answ eralreadydisplayed onthe
screen?AllthosetuplesthathaveSalary morethan????? butnotmorethan?????? are removed
fromthe display? Thisincremental computationismuc hmore e?cientthan recomputingthe view
fromscratc h?
Not allc hangesto theview de?nitionareso easilycomputable?For instance?iftheslider for
Salary ismov ed tolow erthe threshold ofin terestto Salary? ??????then theabove computation
is notpossible?Howev er?we canstill inferthat?a?the oldtuples stillneed tobe displayedand ?b?
somemoretuples needtobeadded?namely?thosetuplesthathavesalarymorethan?????butnot
morethan??????Thus?eventhoughthenewqueryisnotentirelycomputableusingtheanswer
totheoldquery? itispossibletosubstantiallyreducetheamountofrecomputation?
Now?saytheuserdecidestochangeQ
?
byjoiningitwithrelationPandthencomputingan
aggregate?ThatisviewVnowisde?nedbyanewqueryQ
?
?
CREATEVIEWVAS
SELECT Proj??Location?SUM?Salary?
FROME?W?P
WHERESalary??????ANDHours???
GROUPBYProj??Loc ation
?

Page 3

Thus Q
?
requiresthat Q
?
be joinedwith relationP onattribute Pr oj?andtheresultingview
begroup edby Pr oj?andLocation?Notethat thek eyforrelationP is Proj?andPr oj?isalready
intheansw ertoqueryQ
?
?Thus? to computeQ
?
weneedonly lookuptheLocation attribute from
the relationPusing thevalueofProj? foreachtuplein thecurrentanswerset? Theresultingset
oftuples isaggregated overtherequired attributestocomputethe answ ertoqueryQ
?
?
Finally?saytheuser changes viewVtocomputethesumofsalaries foreachLoc ationthat
appearsin Q
?
?Theanswertothis query?callitQ
?
?iscomputable usingonlytheresultof Q
?
?
Becausethegroupingattributesof Q
?
areasupersetofthe groupingattributesofQ
?
?eachgroup of
Q
?
isasubgroupofagroupin Q
?
?Thus?multipletuplesinthe result ofQ
?
arecom binedtogether
tocompute theanswerto Q
?
??
Wefo cusonchangingasingle materializedview?andon recomputingthe newmaterialization
using theold materializationandthe baserelations? Inthispap erwe donot considerho wmultiple
materializedviews maybeusedtofurtherassistthe adaptationprocess?
???Results
Wede?ne theprocess ofrede?ningaviewasasequenceof localc hangesin theviewde?nition? The
adaptationis expressedasan additionalqueryorupdateup ontheold viewand the baserelations
that needstobe executedto adapttheview inresp onseto the rede?nition?We identifya basic
setof localchanges sothata sequenceof localchangescanbe maintained byconcatenating the
main tenancepro cessforeachlocalc hange?Inalmost allcases?thisconcatenationcan beperformed
withoutmaterializingthe in termediateresults? yieldinga singleadaptationmethodfor arbitrary
changestoaview de?nition?
We presentacompr ehensive study ofdi?erentt ypesof localchangesthatcanbemadetoa
view? andpresent algorithmsto main tain theviewsin resp onsetothesechanges? These algorithms
integrate smoothly withacost?based query optimizer? Theoptimizer considersthe additionalplans
pro videdbythealgorithmsanduses one ofthem ifits cost islow erthanthecostofrematerializing
theview?
Weshowthat themaintenance in response toarede?nition isfacilitatedbyk eepingasmall
amount ofextrainformation?beyondtheviewde?nition?sattributesthemselves??Weonlyconsider
informationthatcanbemaintainede?ciently?andshowhowtheadaptationprocesscanbemade
farmoree?cientwiththisinformation?
Ourworkshowsthat?a? itisoften signi?cantlybettertousepreviouslymaterializedviews?and
?b?ifyouknow inadvancethatyoumightchangetheviewsincertainways? thenyoucaninclude
appropriatekindsofadditionalinformationintheviews?
Wethenpresen ta thoroughexperimen talevaluationoftheadaptationtechniques?Theresults
supportouranalysisandalsoleadtosome interestingobservationsaboutphysicaldesignfor
materializedviews?
???RelatedWork
The problemofrede?ningmaterializedviewsisrelatedto theproblemofoptimizinganarbitrary
querygiventhatthedatabasehasmaterializedaviewV?Thequerycanbeconsideredtobe
arede?nitionoftheviewVandonemaycomputethequerybychangingthematerializationof
V?However?thereisanimportantdi?erence?Consideraquerythatreturnsallthetuplesin
the viewexceptone?Whenframedasaqueryoptimizationproblem?thecomplexityofusingthe
viewisO?jVj??wherejVjisthecardinalityofthematerializationofV?Whenframedasaview
adaptationproblem?thecomplexityofthemaintenanceprocessisO?log?jVj??sincewecansimply
delete one tuplefromV?Thiswillimpactthechoiceofthestrategiesforqueryansweringand
?

Page 4

viewadaptationdi?erently?Further?theviewadaptationapproachloses theoldmaterialized view?
whilethe queryingapproachk eepsthe oldview in storage?Thusviewadaptationis notjusta
special caseof theproblemofansweringaquery givensome materializedviews? Ifweomit in?place
up dates? thisisasp ecialcase ofansweringqueries using materializedviews?Butin?place updates
maylead to moree?cientsolutions?
Viewadaptationdi?ersfrom theproblemofusingmaterialized viewsto answer queries also
inthat adaptationassumesthenew viewde?nitionis ?close?to the oldviewde?nition?inthe
sensethat theviewchangesviaa smallset oflo calchanges? Thereisnosuchassumptioninthe
query?answ eringproblem?which meansthata querycompiler?optimizer would haveto spenda
considerable timedetermining howto usethe existing viewstocorrectlyansw eragiv enquery?
Thus?adaptation considersa smallersearch spaceand yieldsasmallerbutmore e?cientset of
standardtechniques thatareeasily incorp orated inrelational systems?
Classic?BBMR???isa systemdev elopedatAT?T BellLaboratories that allows users to
de?nenewconceptsandoptimizestheev aluationoftheir extentsbyclassifying theconceptsin
a concepthierarchy? andthen computingthem starting withthe parent concepts?Thiscorresp onds
toev aluatinga new Classicquery?the new concept??usinginformation in severalmaterialized
views ?theold concepts??Classic hasb eenused for data archaeology?
?LY??? YL??? lo ok at the question ofansw ering queriesusingcachedresults or materialized
views??L Y??? YL???sho w how to transforman SPJ?select?pro ject?join?query so that itisexpressed
completelyusinga giv en setofviews? withoutanyreferenceto thebaserelations? Theyalsohave
the ideaof augmented views whereeachview isextended withk eys ofthe underlyingbaserelations?
?CKPS??? tackle thebroader problem of tryingto answ er any query given any setofview
de?nitions?Becausethey lo ok atthismore general problem?theyhaveam uch largersearch space
?exp onential size? intheiroptimization algorithm?We haveasimple small setofextra plansto
check?F or theless generalproblemwe can do more?anddo itmore e?cien tly?
?RSU???LMSS???also tackletheproblemofansw eringaquerygivenanysetof viewde?nitions?
Theydonotconsideraggregatequeries?Subsequently??DJLS???GHQ???discusshowtoanswer
aggregatequeriesusingmaterializedaggregateviews?Theirresults subsumetheresultspresen ted
inSection ??
?TSI???focusesonthebroaderissueofenhancingphysicaldataindependenceusing?gmaps??
Theyusealogicalschemaand thenspecifytheunderlying physicalstoragestructures asresultsof
?gmap?querieson thelogical schema?Userqueriesonthelogicalschemaarerewrittenusingone
ormoregmapqueriesthateachcorrespondantoaccesstoaphysicalstructures?Thegmapand
userqueriesareSPJexpressions?Querytranslationissimilartousingonlyexistingviews?gmaps?
tocomputenewviews?userqueries??
?TheSystem Model
???Notation
WeconsidersimpleSQL SELECT?FROM?WHEREviews?inadditiontoviewsde?nableusing UNION?
di?erence?EXCEPT?and aggregation?GROUPBY??Weuseasyn tacticshorthand???toav oidhaving
to writedown alltheequalityconditionsinanatural join??Equivalently?one couldusethe
?NA TURALJOIN?keywordspro videdin SQL???
SELECTA
?
?????A
n
FROMR
?
?????R
m
WHEREC
?
AND???ANDC
k
?
Whenthe relationsintheFROMclause are separatedby ampersandsrather than commas?wemean
thattherelationsR
?
?????R
n
arecombinedbyanaturaljoinov erallattributesthatarementioned
?

Page 5

in morethan onerelation? Ifwew antanequijointhatisnotanaturaljoin?weshallspecify
the equijoincondition in theFROM clauseratherthan intheWHERE clause? inside squarebrack ets?
Joinconditions thatarenotequijoins ornatural joins willbespeci?ed intheWHEREclause?The
conditionsC
?
?????C
k
are basic? i?e??non?conjunctiveconditions?The orderin whichwe write the
conditionsorthe relations isnot important?
Whenweperformsc hemachanges?we use standardSQL??ALTERTABLE? and?UPDATE?
statements?
Relationswillbeof twotypes? baserelationsand view relations?Base relationsare physically
storedbythesystem?and areup dateddirectly?The viewrelationsarede?nedasviews?i?e?
queries?ov erbase relationsandotherviewrelations?Amaterializedviewrelationhasits extension
physically storedbythe system?Materializedviewsarenotup dateddirectly?updates on thebase
relationsandother view relations aretranslatedbya viewmain tenancealgorithm into updates to
the materializedview?
De?nition???? KeyAttributes?A key ofarelation Risa minimalsubset ofthe attributesof
R thatuniquely iden ti?estuples inR??
Arelation mayhave severalk eys?and an y one ofthesecouldbeusedinan y oftheresultswe
derive?Key informationwillbe usedintheanalysisforviewchanges?
Adaptation andRecomputation WhenviewVis rede?ned?letthe newde?nitionbecalled
V
?
?When theextentofV
?
isobtainedutilizingthe previouslymaterializedextent ofviewV?the
processwillbe calledadapting viewV? When theextent ofV
?
is obtainedby ev aluating theview
de?nition? withoututilizingthepreviously materialized extentof viewV? theprocess willbe called
rec omputingviewV?We canlo ok uponarecomputationasasp ecialcaseofadaptation where the
previouslymaterializedextent ofviewVisnot usedpro?tably?
??? ViewAdaptationIssues
We maketheminimalisticassumptionthattherede?nitionisexpressedasasequenceofprimitive
localchanges?Eachlocalchangeisasmallchangetotheviewde?nition?Forexample?dropping
orchangingaselectionpredicate?addinganattributetotheresult?changingthe groupinglist?
andaddinga joinrelationareallexamplesoflocalc hanges?Weshallconsidersequencesoflocal
changes?withoutnecessarilymaterializingintermediateresults?inSection??
Givenarede?nableview?the systemand?orthedatabaseadministratorhas to?rst determine
?a? whether the viewshouldbeaugmentedwithsomeextrainformationtohelpwithlateradapta?
tion??b?howthematerializedviewshouldbestored?maybekeepsomefreespaceforeachtupleto
grow?ma ybephysicallyordertheviewbya particularattribute??and?c?whetherthematerialized
viewshouldbeindexed?
Aviewcanbeaugmentedonlyby addingmoreattributesand?ormore tuples?Thus?theoriginal
viewhastobeaselection and?orprojectionoftheaugmentedview?Theadditionalattributesmay
be usefulto adaptthe viewin responsetochangingselections?projections?grouping? andunions?
Next?astheuserrede?nesaview?therede?nitionistranslatedintothesequenceofprimitive
changes?andthesystemmustanalyzetheaugmentedviewandtherede?nitionchangestodetermine
???whetherthe augmentedviewcanbeadapted?and???thevariousalgorithmsforadaptingthe
augmentedview?TheadaptationalgorithmscanalsobeexpressedinSQL?F orexample?the
rede?nedviewcanbematerializedasanSQLqueryovertheoldviewandthebaserelations?
Alternatively?therede?nedviewcanbede?nedbyoneormore SQLinserts?deletesandupdates
intotheoldmaterializationoftheview?orevenbysimplyrecomputingtheviewfrombaserelations?
Thesystem canuseanoptimizertochoosethemostcost?e?ectivealternativeforadaptingtheview?
?