Flexible fragmentation strategy in Informix Dynamic ServerWord格式.docx
- 文档编号:20941252
- 上传时间:2023-01-26
- 格式:DOCX
- 页数:13
- 大小:19.57KB
Flexible fragmentation strategy in Informix Dynamic ServerWord格式.docx
《Flexible fragmentation strategy in Informix Dynamic ServerWord格式.docx》由会员分享,可在线阅读,更多相关《Flexible fragmentation strategy in Informix Dynamic ServerWord格式.docx(13页珍藏版)》请在冰豆网上搜索。
CA"
)indbspace2,
WA"
)indbspace3,
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="
partitionca_part(state="
partitionwa_part(state="
partitionny_part(state="
)indbspace1;
Tableandindexcreation
ADBAcancreateanewtableoranindexwithmultiplefragmentsinasingledbspace,asshownbelow.Thepartitionkeywordhasbeenintroducedtodefinefragmentstrategy.
Example3.Createtablecase
remainderindbspace1;
Example4.Createindexcase
createindexstate_indoncustomer(state)fragmentbyexpression
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
remainderindbspace4;
createindexstate_indoncustomer(state)fragmentbyexpression
Theserveralsosupports'
PARTITIONBYEXPRESSION'
insteadof'
FRAGMENTBYEXPRESSION'
and'
PARTITIONBYROUNDROBIN'
FRAGMENTBYROUNDROBIN'
inallstatementswiththesingledbspacefragmentstrategy.
Example7.'
PARTITIONBY'
cases
createtablecustomer(idint,statechar
(2))partitionbyexpression
createtablecustomer(idint,statechar
(2))partitionbyroundrobin
Alterfragment
ADBAcaneasilyupdateexistingfragmentationmethodsusingthealterfragmentcommand.Thesemodificationcanbeappliedtobothtableandindexfragmentation.ADBAcanmodifyexistingfragmentstrategiesusingalterfragmentoptions.Theseoptionshelptocreatemixfragmentstrategiesbasedonrequirements.Thefollowingexampleshowstheusageofalltablespecificoptions,whichareadd,drop,attach,detach,modify,andinit.
Example8.Createtablecase
createtablecustomer(idint,statechar
(2))fragmentbyexpression
partitionaz_partstate="
indbspace1,
partitionca_partstate="
partitionwa_partstate="
partitionny_partstate="
Example9.Alterfragmentcasesforcreatetable
alterfragmentontablecustomeraddpartitionpart_or(state="
OR"
)
indbspace1beforeca_part;
alterfragmentontablecustomerdroppartitionpart_or;
alterfragmentontablecustomerattachcustomer_oraspartitionpart_3(state="
);
alterfragmentontablecustomerdetachpartitionpart_3customer_or;
alterfragmentontablecustomermodifypartitionaz_parttopartitionpart_az
)indbspace2;
alterfragmentontablecustomerinitfragmentbyexpression
remainderindbspace3;
Example10.Alterfragmentcasesforroundrobin
alterfragmentontablecustomeraddpartitionpart_orindbspace1;
alter
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Flexible fragmentation strategy in Informix Dynamic Server
链接地址:https://www.bdocx.com/doc/20941252.html