tailieunhanh - Using Oracle Data Storage Structures Efficiently

After completing this lesson, you should be able to do the following: Compare and evaluate the different storage structures Examine different data access methods Implement different partitioning methods | Using Oracle Data Storage Structures Efficiently Objectives After completing this lesson, you should be able to do the following: Compare and evaluate the different storage structures Examine different data access methods Implement different partitioning methods Data Storage Structures Cluster Index-organized table Heap table Organization by value Heap Sorted Clustered Partitioned table 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 Selecting the Physical Structure The DBA’s goal is to enable reads and writes to happen as fast as possible. To achieve this goal, the following factors must be taken into account: Rows Read in Groups: If the application uses rows in groups then you should investigate storing the rows in clusters. Also, because clusters do not perform well with high DML activity, . | Using Oracle Data Storage Structures Efficiently Objectives After completing this lesson, you should be able to do the following: Compare and evaluate the different storage structures Examine different data access methods Implement different partitioning methods Data Storage Structures Cluster Index-organized table Heap table Organization by value Heap Sorted Clustered Partitioned table 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 Selecting the Physical Structure The DBA’s goal is to enable reads and writes to happen as fast as possible. To achieve this goal, the following factors must be taken into account: Rows Read in Groups: If the application uses rows in groups then you should investigate storing the rows in clusters. Also, because clusters do not perform well with high DML activity, you should look at the level of DML activity. To distribute the workload among the disks and controllers, you should know which tables will have a high number of reads and which tables will have a high number of writes. Avoid having highly accessed tables on the same drive or controller. Because queries can hit any portion of the table, look for specific hot spots. DML is more likely to have the active extent as the hot spot. Table Size: Consider using a separate tablespace for large tables. For very large partitioned tables, multiple tablespaces can be used. This arrangement assists in managing and distributing the workload evenly among disks. Selecting the Physical Structure (continued) Row Size, Row Groups, and Block Size The Oracle9i database allows multiple block sizes within the same database; thus tables that have a larger row size can have a larger block size. A larger block size can also assist if the application uses full table scans or if the table is clustered. Small or .