吉林大学大型数据库设计与实现dba-12.ppt

ManagingIndexes

ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:ListthedifferenttypesofindexesandtheirusesCreatevarioustypesofindexesReorganizeindexesDropindexesGetindexinformationfromthedatadictionaryMonitortheusageofanindex

ClassificationofIndexesLogicalSinglecolumnorconcatenatedUniqueornonuniqueFunction-basedDomainPhysicalPartitionedornonpartitionedB-treeNormalorreversekeyBitmap

B-TreeIndexIndexentryheaderKeycolumnlengthKeycolumnvalueROWIDRootBranchLeafIndexentry

BitmapIndexBlue,10.0.3,12.8.3,1000100100010010100Green,10.0.3,12.8.3,0001010000100100000Red,10.0.3,12.8.3,0100000011000001001Yellow,10.0.3,12.8.3,0010001000001000010keystartROWIDendROWIDbitmapTableIndexBlock10Block11Block12File3

ComparingB-Treeand

BitmapIndexesB-tree Suitableforhigh-cardinality

columnsUpdatesonkeysrelatively

inexpensiveInefficientforqueries

usingORpredicates

UsefulforOLTPBitmapSuitableforlow-cardinality

columnsUpdatestokeycolumnsvery

expensiveEfficientforqueries

usingORpredicates

Usefulfordatawarehousing

CreatingNormalB-TreeIndexesCREATEINDEXhr.employees_last_name_idxONhr.employees(last_name)PCTFREE30STORAGE(INITIAL200KNEXT200K PCTINCREASE0 MAXEXTENTS50)TABLESPACEindx;

CreatingIndexes:GuidelinesBalancequeryandDMLneedsPlaceinseparatetablespaceUseuniformextentsizes:MultiplesoffiveblocksorMINIMUMEXTENTsizefortablespaceConsiderNOLOGGINGforlargeindexesINITRANSshouldgenerallybehigheronindexesthanonthecorrespondingtables.

CreatingBitmapIndexesUsetheparameterCREATE_BITMAP_AREA_SIZEtospecifytheamountofmemoryallocatedforbitmapcreation.CREATEBITMAPINDEXorders_region_id_idxONorders(region_id)PCTFREE30STORAGE(INITIAL200KNEXT200K PCTINCREASE0 MAXEXTENTS50)TABLESPACEindx;

ChangingStorageParametersforIndexesALTERINDEXemployees_last_name_idxSTORAGE(NEXT400K MAXEXTENTS100);

Allo