Oracle-9i-数据库性能调优技术-les14

UsingOracleDataStorageStructuresEfficientlyObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:CompareandevaluatethedifferentstoragestructuresExaminedifferentdataaccessmethodsImplementdifferentpartitioningmethodsDataStorageStructuresClusterIndex-organizedtableHeaptableOrganizationbyvalueHeapSortedClusteredPartitionedtableSelectingthePhysicalStructureFactorsaffectingtheselection:RowsreadingroupsSELECTorDMLstatementsTablesizeRowsize,rowgroup,andblocksizeSmallorlargetransactionsUsingparallelqueriestoloadorforSELECTstatementsDataAccessMethodsToenhanceperformance,youcanusethefollowingdataaccessmethods:ClustersIndexesB-tree(normalorreversekey)BitmapFunctionbasedIndex-organizedtablesMaterializedviewsClustersClusteredordersandorder_itemtablesClusterKey(ORD_NO)101ORD_DTCUST_CD05-JAN-97R01PRODQTYA410220A567519W082410102ORD_DTCUST_CD07-JAN-97N45PRODQTYA209111G783020N958726Unclusteredordersandorder_itemtablesORD_NOPRODQTY...-----------------101A410220102A209111102G783020102N958726101A567519101W082410ORD_NOORD_DTCUST_CD------------------10105-JAN-97R0110207-JAN-97N45ClusterTypesIndexclusterHashclusterHashfunctionSituationsWhereClustersAreUsefulCriterionUniformkeydistributionEvenlydistributedkeyvaluesRarelyupdatedkeyOftenjoinedmaster-detailtablesPredictablenumberofkeyvaluesQueriesusingequalitypredicateonkeyHashXXXXXIndexXXXPartitioningMethodsThefollowingpartitioningmethodsareavailable:RangeHashListCompositeRangepartitioningHashpartitioningCompositepartitioningListpartitioning123CREATETABLEsales(acct_noNUMBER(5),personVARCHAR2(30),sales_amountNUMBER(8),week_noNUMBER(2))PARTITIONBYRANGE(week_no)(PARTITIONP1VALUESLESSTHAN(4)TABLESPACEdata0,PARTITIONP2VALUESLESSTHAN(8)TABLESPACEdata1,...…PARTITIONP13VALUESLESSTHAN(53)TABLESPACEdata12);Thepartitionkeyisweek_no.VALUESLESSTHANmustbespecifiedasaliteral.Physicalattributescanbesetperpartition.123RangePartitioningExampleHashPartitioningOverviewEasytoImplementEnablesbetterperformanceforPDMLandpartition-wisejoinsInsertsrowsintopartitionsautomaticallybasedonthehashofthepartitionkeySupports(hash)localindexesDoesnotsupport(hash)globalindexesListPartitioningExampleSQL>CREATETABLElocations2(location_id,street_address,3postal_code,city,state_province,4country_id)5STORAGE(INITIAL10KNEXT20K)6TABLESPACEusers7PARTITIONBYLIST(state_province)8(PARTITIONregion_east9VALUES('MA','NY','CT','ME','MD'),10PARTITIONregion_west11VALUES('CA','AZ','NM','OR','WA'),12PARTITIONregion_south13VALUES('TX','KY','TN','LA','MS'),14PARTITIONregion_central15VALUES('OH','ND','SD','MO','IL'));DefaultPartitionforListPartitioningCreateaDEFAULTlistpartitionforallvaluesnotcoveredbyotherpartitions:CREATETABLEcustomer...PARTITIONBYLIST(state)(PARTITIONp1VALUES('CA','CO'),PARTITIONp2VALUES('FL','TX'),PARTITIONp3VALUES(DEFAULT));CompositePartitionedTableOverviewIdealforbothhistoricaldataanddataplacementProvideshighavailabilityandmanageability,likerangepartitioningImprovesperformanceforparallelDMLandsupportspartition-wisejoinsAllowsmoregranularpartitioneliminationSupportscompositelocalindexesDoesnotsupportcompositeglobalindexesGlobalNonpartitionedindexLocalpartitionedindexGlobalPartitionedIndexPartitionedIndexesforScalableAccessTablepartitionTablepartitionTablepartitionTablepartitionPartitionPruningPartitionpruning:Onlytherelevantpartitionsareaccessed.99-May99-Apr99-Feb99-Jan99-Mar99-JunsalesSQL>SELECTSUM(sales_amount)2FROMsales3WHEREsales_dateBETWEEN4TO_DATE(‘01-MAR-1999’,5‘DD-MON-YYYY’)AND6TO_DATE(‘31-MAY-1999’,7‘DD-MON-YYYY’);Nonpartition-wisejoinFullpartition-wisejoinPartialpartition-wisejoinQueryslavePartitionPartitionedtable123Partition-WiseJoinStatisticsCollectionforPartitionedObjectsYoucangatherobject-,partition-,orsubpartitionlevelstatistics.ThereareGLOBALorNON-GLOBALstatistics.Thedbms_statspackagecangatherglobalstatisticsatanylevelfortablesonly.Itisnotpossibletogather:GlobalhistogramsGlobalstatisticsforindexesCALLdbms_stats.gather_table_stats(ownname=>‘o901’,tabname=>‘sales’,partname=>‘feb99’,granularity=>‘partition’);CALLdbms_stats.gather_index_stats(ownname=>‘o901’,indname=>‘isales’,partname=>‘s1’);Somedbms_statsExamplesSummaryInthislesson,youshouldhavelearnedhowtodothefollowing:CompareandevaluatethedifferentstoragestructuresExaminedifferentdataaccessmethodsImplementdifferentpartitioningmethods*以上有不当之处，请大家给与批评指正，谢谢大家！