欢迎来到天天文库
浏览记录
ID:40103197
大小:150.38 KB
页数:24页
时间:2019-07-21
《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®
此文档下载收益归作者所有