BitmapIndexInternals

1juliandyke.comBitmapIndexInternalsJulianDykeIndependentConsultantWebVersion©2005JulianDyke©2005JulianDykejuliandyke.com2AgendaIntroductionBitmapRowSourcesInternalStructuresBitmapIndexDMLConclusion©2005JulianDykejuliandyke.com3Introduction©2005JulianDykejuliandyke.com4BitmapIndexesIntroducedinOracle7.3OriginallyintendedforcolumnswithLowcardinality(fewdistinctvalues)LittleornoDMLactivityOftenusedinDSSenvironmentsUsefulforad-hocquerieswherepredicatesarenotknowninadvance©2005JulianDykejuliandyke.com5BitmapIndexPropertiesBitmapindexesMustbenon-uniqueIncludeNULLvalues(B*Treeindexesdonot)Maximum30columns(B*Treeindexesmax32columns)Canbelocallypartitioned(8.1.5andabove)CannotbegloballypartitionedCanbefunction-basedindexes(built-inoruser-defined)CanbeIOTsecondaryindexes(9.2andabove)CanbecreatedonglobaltemporarytablesCannotbecompressedCannotbereversed©2005JulianDykejuliandyke.com6BitmapIndexesCREATETABLEproperty(property_codeNUMBER,bedroomsNUMBER,receptionsNUMBER,garagesNUMBER);CREATEBITMAPINDEXindex1ONproperty(bedrooms);CREATEBITMAPINDEXindex2ONproperty(receptions);CREATEBITMAPINDEXindex3ONproperty(garages);SELECTproperty_codeFROMpropertyWHEREbedrooms=4ANDreceptions=3ANDgarages=2Forexample©2005JulianDykejuliandyke.com7LogicalOperationsBitmapscanbecombinedusingthelogicaloperationsAND,OR,NOT.ABAANDBAORBNOTA00001010111001011110OraclealsoimplementsaMINUSoperationinternallyAMINUSBisequivalenttoAANDNOTB©2005JulianDykejuliandyke.com8MulticolumnBitmapIndexesBitmapindexescanbedefinedformorethanonecolumnCREATEBITMAPINDEXi1ONt1(c1,c2);CanbeusedforqueriessuchasSELECT*FROMt1WHEREc1=0;SELECT*FROMt1WHEREc1=0ANDc2=0;HoweverforqueriessuchasSELECT*FROMt1WHEREc2=0;thereisapparentlynoequivalenttoINDEX(SKIPSCAN)ConsidercreatingtwoindexesandallowingOracletoperformjoindynamicallyCREATEBITMAPINDEXi1ONt1(c1);CREATEBITMAPINDEXi2ONt1(c2);©2005JulianDykejuliandyke.com9BitmapRowSources©2005JulianDykejuliandyke.com10BitmapRowSourcesOperationVersionBITMAPINDEX(SINGLEVALUE)7.3.2BITMAPINDEX(RANGESCAN)7.3.2BITMAPINDEX(FULLSCAN)7.3.2BITMAPINDEX(FASTFULLSCAN)9.0.1BITMAPAND7.3.2BITMAPOR7.3.2BITMAPMINUS7.3.2BITMAPMERGE7.3.2BITMAPKEYITERATION7.3.2BITMAPCONVERSIONTOROWIDS7.3.2BITMAPCONVERSIONCOUNT7.3.2BITMAPCONVERSIONFROMROWIDS7.3.2BITMAPCONSTRUCTION7.3.2BITMAPCOMPACTION7.3.2©2005JulianDykejuliandyke.com11BitmapIndexOperationIntroducedin7.3OptionsareBITMAPINDEX(SINGLEVALUE)BuildsasinglebitmapforagivenvalueBITMAPINDEX(RANGESCAN)BuildsabitmapcontainingeachvalueintherangeBITMAPINDEX(FULLSCAN)BuildsabitmapcontainingeachvalueintheindexBITMAPINDEX(FASTFULLSCAN)Oracle9.0.1andaboveEquivalenttoINDEX(FASTFULLSCAN)©2005JulianDykejuliandyke.com12BitmapConversionOperationIntroducedinOracle7.3OptionsareBITMAPCONVERSION(TOROWIDS)ConvertsabitmapintoasetofROWIDsBITMAPCONVERSION(COUNT)ReturnsthenumberofsetbitsinabitmapUsedtoimplementCOUNT()aggregatesBITMAPCONVERSION(FROMROWIDS)ConvertsasetofROWIDsintoabitmap©2005JulianDykejuliandyke.com13BitmapAndOperationIntroducedinOracle7.3PerformslogicalANDoperationbetweentwobitmapsCREATETABLEt1(c1NUMBER,c2NUMBER);CREATEBITMAPINDEXi1ONt1(c1);CREATEBITMAPINDEXi2ONt1(c2);0SELECTSTATEMENTOptimizer=CHOOSE10BITMAPCONVERSION(TOROWIDS)21BITMAPAND32BITMAPINDEX(SINGLEVALUE)OF'I1‘42BITMAPINDEX(SINGLEVALUE)OF'I2‘SELECT/*+INDEX_COMBINE(t1i1i2)*/c1,c2FROMt1WHEREc1=0ANDc2=0;©2005JulianDykejuliandyke.com14BitmapOrOperationIntroducedinOracle7.3PerformslogicalORoperationbetweentwobitmapsCREATETABLEt1(c1NUMBER,c2NUMBER);CREATEBITMAPINDEXi1ONt1(c1);CREATEBITMAPINDEXi2ONt1(c2);0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(BYINDEXROWID)OF'T1‘21BITMAPCONVERSION(TOROWIDS)32BITMAPOR43BITMAPINDEX(SINGLEVALUE)OF'I1‘53BITMAPINDEX(SINGLEVALUE)OF'I2'SELECT/*+INDEX_COMBINE(t1i1i2)*/c1,c2FROMt1WHEREc1=0ORc2=0;©2005JulianDykejuliandyke.com15BitmapMinusOperationIntroducedinOracle7.3PerformslogicalMINUSoperationbetweentwobitmapsCREATETABLEt1(c1NUMBER,c2NUMBER);CREATEBITMAPINDEXi1ONt1(c1);CREATEBITMAPINDEXi2ONt1(c2);0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(BYINDEXROWID)OF'T1‘21BITMAPCONVERSION(TOROWIDS)32BITMAPMINUS43BITMAPMINUS54BITMAPINDEX(SINGLEVALUE)OF'I1‘64BITMAPINDEX(SINGLEVALUE)OF'I2‘73BITMAPINDEX(SINGLEVALUE)OF'I2'SELECT/*+INDEX_COMBINE(t1i1i2)*/c1,c2FROMt1WHEREc1=0ANDNOTc2=0;©2005JulianDykejuliandyke.com16BitmapMergeOperationIntroducedinOracle7.3MergestwoormorebitmapstogetherCREATETABLEt1(c1NUMBER,c2NUMBER);CREATEBITMAPINDEXi1ONt1(c1);CREATEBITMAPINDEXi2ONt1(c2);0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(BYINDEXROWID)OF'T1'21BITMAPCONVERSION(TOROWIDS)32BITMAPAND43BITMAPINDEX(SINGLEVALUE)OF'I2‘53BITMAPMERGE65BITMAPINDEX(RANGESCAN)OF'I1'SELECT/*+INDEX_COMBINE(t1i1i2)*/c1,c2FROMt1WHEREc10ANDc2=0;©2005JulianDykejuliandyke.com17BitmapConstruction/CompactionIntroducedinOracle7.3Usedbyindexcreation/rebuildoperationsAppearinEXPLAINPLANDonotappearinAUTOTRACEorV$SQL_PLANCREATETABLEt1(c1NUMBER);0CREATEINDEXSTATEMENTOptimizer=CHOOSE10INDEXBUILD(NONUNIQUE)OF'I1‘21BITMAPCOMPACTION32SORT(CREATEINDEX)43BIT