資源描述：
《temporary file and tables》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在學(xué)術(shù)論文-天天文庫。
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?
