Execution Plan Operations

《Execution Plan Operations》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。

1、ALTERINDEXSTATEMENTDescriptionALTERINDEXstatementVersionsThisoperationisimplementedinthefollowingversions8.1.79.0.19.2.0ExampleThisexamplewasdevelopedusingOracle9.2.0.1onWindows2000ThisoperationcannotbedetectedusingAUTOTRACE.UseEXPLAINPLANinsteadCREATETABLEt1(c1NUMBER);CREATEINDEXi1ONt1(c1);EX

2、PLAINPLANFORALTERINDEXi1REBUILD;Thisstatementcreatesthefollowingexecutionplan(utlxpls.sql)0ALTERINDEXSTATEMENT10INDEXBUILD(NONUNIQUE)I121SORT(CREATEINDEX)32TABLEACCESS(FULL)OF'T1'AND-EQUALDescriptionJoinstwoormorenon-uniqueindexesonthesametableVersionsThisoperationisimplementedinthefollowingve

3、rsions7.3.48.0.48.0.58.0.68.1.58.1.68.1.79.0.19.2.0ExampleThisexamplewasdevelopedusingOracle9.2.0.1onWindows2000ThisexamplerequiresthefollowingtableandindexdefinitionsCREATETABLEt1(c1NUMBERNOTNULL,c2NUMBERNOTNULL,c3NUMBERNOTNULL);CREATEINDEXi1ONt1(c2);CREATEINDEXi2ONt1(c3);ANALYZETABLEt1COMPUT

4、ESTATISTICS;ThestatementSELECT/*+AND_EQUAL(t1i1i2)*/c1FROMt1WHEREc2=0ANDc3=0;generatesthefollowingexecutionplan0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(BYINDEXROWID)OF'T1'21AND-EQUAL32INDEX(RANGESCAN)OF'I1'(NON-UNIQUE)42INDEX(RANGESCAN)OF'I2'(NON-UNIQUE)BITMAPANDDescriptionSelectsrowsfrom

5、atablebasedontheresultofapplyinganANDontwobitmapindexesVersionsThisoperationisimplementedinthefollowingversions7.3.48.0.48.0.58.0.68.1.58.1.68.1.79.0.19.2.0ExampleThisexamplewasdevelopedusingOracle9.2.0.1onWindows2000ThisexamplerequiresthefollowingtableandindexdefinitionsCREATETABLEt1(c1NUMBER

6、,c2NUMBER);CREATEBITMAPINDEXi1ONt1(c1);CREATEBITMAPINDEXi2ONt1(c2);ThetabledoesnotneedtobeanalysedThestatementSELECT/*+INDEX_COMBINE(t1i1i2)*/c1,c2FROMt1WHEREc1=0ANDc2=0;generatesthefollowingexecutionplan0SELECTSTATEMENTOptimizer=CHOOSE10BITMAPCONVERSION(TOROWIDS)21BITMAPAND32BITMAPINDEX(SINGL

7、EVALUE)OF'I1'42BITMAPINDEX(SINGLEVALUE)OF'I2'BITMAPCOMPACTIONDescriptionCompressesabitmapduringbitmapindexcreationVersionsThisoperationwasintroducedinOracle8.0.4Thisoperationisimplementedinthefollowingversions8.0.48.0.58.0.68.1.58.1.68.