temporary file and tables

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

1、Module15TemporaryFilesandTablesTemporaryFilesandTables03-200315-1©2001,2003InternationalBusinessMachinesCorporationObjectivesAttheendofthismodule,youwillbeableto:®Describethetemporaryobjectscreatedbythedatabaseserver®Describetheconfigurationparametersandenviron

2、mentvariablesrelatedtotemporaryobjects®Explaintheimportanceofusingmultipletemporarydbspaces®ExplainthepurposeoftheWITHNOLOGclausewhenusedtocreatetemporarytables®UseonstatandtheSMItomonitorthespaceusedtocreatetemporaryobjects15-215-2TemporaryFilesandTablesTypeso

3、fTemporaryObjects¢Temporaryfiles®ORDERBYorGROUPBYinaSELECTstatement®UNIQUEorDISTINCTinaSELECTstatement®Sort-mergeandhashjoin®Indexbuilds®Warmrestore(logicallogfiles)¢Temporarytables®Implicit(SELECTINTOTEMP)®Explicit(CREATETEMPTABLE)®Blobvaluesarereceivedfromand

4、passedtostoredproceduresandareusedasglobalvariables15-3DynamicServerusestwotypesoftemporaryobjectstostoretemporarydata:temporaryfilesandtemporarytables.Atemporaryfileiscreatedunderthefollowingcircumstances:¢AnORDERBYorGROUPBYclauseinaSELECTstatementcannotuseani

5、ndextoordertheresultsandasortisrequired.¢TheUNIQUEorDISTINCTstatementisusedinaSELECTstatementtoeliminateduplicates.¢Thesort-mergejoinisusedtojointwotablesinaSELECTstatementandanindexcannotbeused.¢Ahashjoinmustcreateahashtableandthereisnotenoughspaceavailableins

6、haredmemory.¢Anindexbuildneedstosortthekeysbeforetheyareputintheindex.¢Temporaryfilesareusedtoholdlogicallogfilesduringawarmrestore.Temporarytablesarecreatedunderthefollowingcircumstances:¢TheINTOTEMPclauseisusedaspartoftheSELECTstatement.¢TheCREATETEMPTABLEsta

7、tementisused.¢Ablobispassedfromtheapplicationprocesstoastoredprocedureinthedatabaseserveroraglobalblobvariableisdefinedandassignedavalue.TemporaryFilesandTables15-3TemporaryObjectConfigurationFortemporarytables,thefollowingprecedenceapplies:®DBSPACETEMPenvironm

8、entvariable®DBSPACETEMPconfigurationparameter®RootdbspaceordbspacewherethedatabasewascreatedFortemporaryfiles,thefollowingprecedenceapplies:®PSORT_DBTEMPenvironmentvariable®