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

Using Oracle Data StorageStructures Efficiently Objectives After completing this lesson, you should be able todo the following: Compare and evaluate the different storage structures Examine different data access methods Implement different partitioning methods Data Storage Structures Selecting the Physical Structure Factors affecting the selection: Rows read in groups SELECT or DML statements Table size Row size, row group, and block size Small or large transactions Using parallel queries to load or for SELECT statements Data Access Methods To enhance performance, you can use the following data access methods: Clusters Indexes B-tree (normal or reverse key) Bitmap Function based Index-organized tables Materialized views Clusters Cluster Types Situations Where Clusters Are Useful Partitioning Methods The following partitioning methods are available: Range Hash List Composite Range Partitioning Example Hash Partitioning Overview Easy to Implement Enables better performance for PDML and partition-wise joins Inserts rows into partitions automatically based on the hash of the partition key Supports (hash) local indexes Does not support (hash) global indexes List Partitioning Example Default Partition for List Partitioning Create a DEFAULT list partition for all values not covered by other partitions: Composite Partitioned Table Overview Ideal for both historical data and data placement Provides high availability and manageability, like range partitioning Improves performance for parallel DML and supports partition-wise joins Allows more granularpartition elimination Supports composite local indexes Does not support compositeglobal indexes Partitioned Indexes for Scalable Access Partition Pruning Statistics Collection forPartitioned Objects You can gather object-, partition-, or subpartition level statistics. There are GLOBAL or NON-GLOBAL statistics. The dbms_stats package can gather global statistics at any level for tables only. It is not possible to gather: Globa