Oracle9i数据库性能调优技术les14

UsingOracleDataStorage

StructuresEfficientlyObjectivesAftercompletingthislesson,youshouldbeableto

dothefollowing:CompareandevaluatethedifferentstoragestructuresExaminedifferentdataaccessmethodsImplementdifferentpartitioningmethodsDataStorageStructuresClusterIndex-organized

tableHeaptableOrganizationbyvalueHeapSortedClusteredPartitioned

tableSelectingthePhysicalStructureFactorsaffectingtheselection:RowsreadingroupsSELECTorDMLstatementsTablesizeRowsize,rowgroup,andblocksizeSmallorlargetransactionsUsingparallelqueriestoloadorforSELECTstatementsDataAccessMethodsToenhanceperformance,youcanusethefollowingdataaccessmethods:ClustersIndexesB-tree(normalorreversekey)BitmapFunctionbasedIndex-organizedtablesMaterializedviewsClustersClusteredordersandorder_itemtablesClusterKey(ORD_NO)101 ORD_DT CUST_CD 05-JAN-97 R01

PROD QTY A4102 20 A5675 19 W0824 10102 ORD_DT CUST_CD07-JAN-97N45

PROD QTY A209111 G783020 N958726Unclusteredordersandorder_itemtables ORD_NO PROD QTY ...

----- ------ ------ 101 A4102 20 102 A2091 11 102 G7830 20 102 N9587 26 101 A5675 19 101 W0824 10 ORD_NO ORD_DT CUST_CD

------ ------ ------ 101 05-JAN-97 R01 102 07-JAN-97 N45

ClusterTypesIndexclusterHashclusterHashfunctionSituationsWhereClustersAreUsefulCriterionUniformkeydistributionEvenlydistributedkeyvaluesRarelyupdatedkeyOftenjoinedmaster-detailtablesPredictablenumberofkeyvaluesQueriesusingequalitypredicateonkeyHashXXXXXIndexXXXPartitioningMethodsThefollowingpartitioningmethodsareavailable:RangeHashListCompositeRange

partitioningHash

partitioningComposite

partitioningList

partitioning123CREATETABLEsales(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-wisejoinsAllowsmoregranular

partitioneliminationSupportscompositelocalindexesDoesnotsupportcomposite

globalindexesGlobalNonpartitionedindexLocalpartitionedindexGlobalPartitionedIndexPartitionedIndexesforScalableAccessTablepartitionTablepartitionTablepartitionTablepartitionPartitionPruningPartitionpruning: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-WiseJoinStatisticsCollectionfor

PartitionedObjectsYoucangatherobject-,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=>‘