Flexible fragmentation strategy in Informix Dynamic Server.docx
- 文档编号:7813287
- 上传时间:2023-01-26
- 格式:DOCX
- 页数:13
- 大小:19.57KB
Flexible fragmentation strategy in Informix Dynamic Server.docx
《Flexible fragmentation strategy in Informix Dynamic Server.docx》由会员分享,可在线阅读,更多相关《Flexible fragmentation strategy in Informix Dynamic Server.docx(13页珍藏版)》请在冰豆网上搜索。
FlexiblefragmentationstrategyinInformixDynamicServer
FlexiblefragmentationstrategyinInformixDynamicServer
Introduction
Considerthekindofapplicationwheredatafragmentationisbasedondateexpression,usingaseparatefragmentforsalesreportforeachday.Eachday,theDBAneedstoaddanewdbspaceforthedateexpression.Iftheapplicationrequiresalargenumberofrangesindifferentfragments,theDBAisrequiredtoadministermultipledbspaces.Dbspacemanagement,includingdbspacecreationanddeletion,addstotheDBA'stasks.
ThefragmentationstrategyofIDS9.4(orearlierreleases)doesnotallowtablestobefragmentedwithmultiplefragmentsinonedbspace.Therefore,eachdatasetrangeneedsaseparatedbspace.ThefollowingexampleshowsacustomertablewherefragmentbyexpressionisbasedontheSTATEcolumn.Eachtablefragmentgivenbelowisassociatedwithaseparatedbspace.Whenyouneedtoaddanewstate,youneedtoaddanewdbspace.
Example1
createtablecustomer(idint,statechar
(2))fragmentbyexpression
(state="AZ")indbspace1,
(state="CA")indbspace2,
(state="WA")indbspace3,
(state="NY")indbspace4;
Byusingmultiplefragmentsinasingledbspace,theDBAcanmanagealargenumberofdatafragmentswithoutlookingforfreespaceinthefilesystemandanewnameforthedbspace.DBAsimplyaddsorattachesthenewexpressionandspecifiesthenewfragmentname.
Fragmentationbackground
TheIBMInformixdatabaseserversupportstableandindexfragmentation(alsocalledpartitioning),allowingyoutostoreatableonmultiplediskdevices.TheproperfragmentationstrategysignificantlyreducesI/Ocontentionandincreasesmanageability.
TheInformixfragmentationstrategyconsistsoftwoparts:
∙Adistributionschemethatspecifieshowtogrouprowsintofragments.YouspecifythedistributionschemeintheFRAGMENTBYclauseoftheCREATETABLE,CREATEINDEX,orALTERFRAGMENTstatements.
∙Thesetofdbspacesinwhichyoulocatethefragments.YouspecifythesetofdbspacesintheINclause(storageoption)oftheseSQLstatements.
Achunkisacontiguoussectionofdiskspaceavailableforadatabaseserver.Adbspaceincludesoneormorechunks.TheDBAcanaddmorechunksatanytime,andshouldmonitordbspaceusageandaddchunksifnecessary.Afragmentationstrategycanbebuiltonatable,anindex,orboth.Afragmentationdistributionschemecanbeeitherexpression-basedorround-robin-based.Expression-basedfragmentationenablesdistributingtherowsintomultiplefragmentsbasedonafragmentexpression(state='AZ'intheaboveexample).Eachfragmentexpressionisolatesarowandaidsinnarrowingthesearchspaceforqueries.Youcandefinerangerulesorarbitraryrulesthatindicatetothedatabaseserverhowrowsaretobedistributed.Theround-robinfragmentationstrategydistributestherowssothatthenumberofrowsineachfragmentremainsapproximatelythesame.
Tablefragment(partition)referstozeroormorerowsthataregroupedtogetherandstoredinadbspacethatyouspecifywhenyoucreatethefragment.Eachtablefragmenthasitsowntblspacewithauniquetblspace_idorfragment_id.
Backtotop
Oldandnewfragmentationstrategies
IDS9.4supportstwostrategies:
fragmentbyexpressionandfragmentbyround-robin.Whilethisisgood,IDS9.4requiresthateachtablefragmentisstoredinadistinctdbspaces.Onedbspacecancontainfragmentsfrommultipletables,butasingletablecannothavemorethanonefragmentinasingledbspace.ThiscausesmanageabilityoverheadrequiringDBAstocreatelargenumberofdbspaces.
LimitationswithIDS9.4(orearlierreleases)fragmentstrategy:
∙Foragiventableoranindex,itcannotstoremultiplefragmentsinasingledbspace.TheDBAhastoaddanewdbspaceforeverynewfragmentexpression.
∙TheDBAhasoverheadtomonitoralargenumberofdbspaces.
∙Limitedchunksperdbspace.
∙Fixedpagesizeforalldbspaces.
AlloftheabovelimitationsareaddressedinIDS10.0byusingmultiplefragmentsinasingledbspace,largechunk,andnon-defaultpagesizesupport.
InIDS10.0,theDBAisabletoconsolidatetablesorindicesonmultipledbspacesintoasingledbspace.Newtablesandindicescanbecreatedwithoneormorefragmentsfromoneormoredbspaces.DBAscanmanagealargenumberoftablesorindexfragmentswithamanageablenumberofdbspaces.InIDS10.0,eachfragmentdistributionschemaisassociatedtoapartition.Existingfragmentationstrategycaneasilybeconvertedintoamultiplefragmentstrategyusingapartitionsyntaxinthealterfragmentcommand.Afragmentedtableorindexcanbecreatedusingoldandnewfragmentationsyntax.MultiplefragmentstrategiesdonotimpacttheParallelDatabaseQuery(PDQ)fromitsoldbehavior.Parallelthreadsareexecutedthesameasoldfragmentationstrategy.OldfragmentationstrategyisstillsupportedinIDS10.0.
Inanewschema,atableusesasingledbspacebutkeepstheoriginalfragmentexpressiononthestatecolumn.Followingaz_part,ca_part,wa_part,andny_partarepartitionsinthenewfragmentationstrategy.
Example2
createtablecustomer(idint,statechar
(2))fragmentbyexpression
partitionaz_part(state="AZ")indbspace1,
partitionca_part(state="CA")indbspace1,
partitionwa_part(state="WA")indbspace1,
partitionny_part(state="NY")indbspace1;
Backtotop
Tableandindexcreation
ADBAcancreateanewtableoranindexwithmultiplefragmentsinasingledbspace,asshownbelow.Thepartitionkeywordhasbeenintroducedtodefinefragmentstrategy.
Example3.Createtablecase
createtablecustomer(idint,statechar
(2))fragmentbyexpression
partitionaz_part(state="AZ")indbspace1,
partitionca_part(state="CA")indbspace1,
partitionwa_part(state="WA")indbspace1,
partitionny_part(state="NY")indbspace1,
remainderindbspace1;
Example4.Createindexcase
createindexstate_indoncustomer(state)fragmentbyexpression
partitionaz_part(state="AZ")indbspace2,
partitionca_part(state="CA")indbspace2,
partitionwa_part(state="WA")indbspace2,
partitionny_part(state="NY")indbspace2,
remainderindbspace2;
Theround-robinfragmentmethodcanbeappliedtothecustomertableusingasingledbspacefragmentstrategy.
Example5.Round-robincase
createtablecustomer(idint,statechar
(2))fragmentbyroundrobin
partitionaz_partindbspace1,
partitionca_partindbspace1,
partitionwa_partindbspace1,
partitionny_partindbspace1;
Ausercanselectthefragmentexpressiontobestoredinasingledbspacebasedonrequirements.Thefollowingexamplesshowhowausercancombineoldandnewfragmentstrategiesduringtableorindexcreation.
Example6.Oldandnewfragmentationmethods
Createtablecustomer(idint,statechar
(2))fragmentbyexpression
partitionaz_part(state="AZ")indbspace1,
partitionca_part(state="CA")indbspace1,
(state="WA")indbspace2,
(state="NY")indbspace3,
remainderindbspace4;
createindexstate_indoncustomer(state)fragmentbyexpression
partitionaz_part(state="AZ")indbspace2,
partitionca_part(state="CA")indbspace2,
partitionwa_part(state="WA")indbspace2,
(state="NY")indbspace3,
remainderindbspace4;
Theserveralsosupports'PARTITIONBYEXPRESSION'insteadof'FRAGMENTBYEXPRESSION',and'PARTITIONBYROUNDROBIN'insteadof'FRAGMENTBYROUNDROBIN'inallstatementswiththesingledbspacefragmentstrategy.
Example7.'PARTITIONBY'cases
createtablecustomer(idint,statechar
(2))partitionbyexpression
partitionaz_part(state="AZ")indbspace1,
partitionca_part(state="CA")indbspace1,
partitionwa_part(state="WA")indbspace1,
partitionny_part(state="NY")indbspace1,
remainderindbspace1;
createtablecustomer(idint,statechar
(2))partitionbyroundrobin
partitionaz_partindbspace1,
partitionca_partindbspace1,
partitionwa_partindbspace1,
partitionny_partindbspace1;
Backtotop
Alterfragment
ADBAcaneasilyupdateexistingfragmentationmethodsusingthealterfragmentcommand.Thesemodificationcanbeappliedtobothtableandindexfragmentation.ADBAcanmodifyexistingfragmentstrategiesusingalterfragmentoptions.Theseoptionshelptocreatemixfragmentstrategiesbasedonrequirements.Thefollowingexampleshowstheusageofalltablespecificoptions,whichareadd,drop,attach,detach,modify,andinit.
Example8.Createtablecase
createtablecustomer(idint,statechar
(2))fragmentbyexpression
partitionaz_partstate="AZ"indbspace1,
partitionca_partstate="CA"indbspace1,
partitionwa_partstate="WA"indbspace1,
partitionny_partstate="NY"indbspace1,
remainderindbspace2;
Example9.Alterfragmentcasesforcreatetable
alterfragmentontablecustomeraddpartitionpart_or(state="OR")
indbspace1beforeca_part;
alterfragmentontablecustomerdroppartitionpart_or;
alterfragmentontablecustomerattachcustomer_oraspartitionpart_3(state="OR");
alterfragmentontablecustomerdetachpartitionpart_3customer_or;
alterfragmentontablecustomermodifypartitionaz_parttopartitionpart_az
(state="AZ")indbspace2;
alterfragmentontablecustomerinitfragmentbyexpression
partitionaz_part(state="AZ")indbspace2,
partitionca_part(state="CA")indbspace2,
partitionwa_part(state="WA")indbspace3,
partitionny_part(state="NY")indbspace3,
remainderindbspace3;
Example10.Alterfragmentcasesforroundrobin
alterfragmentontablecustomeraddpartitionpart_orindbspace1;
alterfragmentontablecustomerdroppartitionpart_or;
alter
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Flexible fragmentation strategy in Informix Dynamic Server
链接地址:https://www.bdocx.com/doc/7813287.html