1 / 20

Using Oracle Data Storage Structures Efficiently

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. Heap table. Cluster.

jiro
Download Presentation

Using Oracle Data Storage Structures Efficiently

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using Oracle Data StorageStructures Efficiently

  2. 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

  3. Heap table Cluster Index-organizedtable Data Storage Structures Organization by value Heap Clustered Sorted Partitionedtable

  4. 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

  5. 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

  6. Clusters Cluster Key (ORD_NO) 101 ORD_DT CUST_CD 05-JAN-97 R01 PROD QTY A4102 20 A5675 19 W0824 10 102 ORD_DT CUST_CD 07-JAN-97 N45 PROD QTY A2091 11 G7830 20 N9587 26 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 Unclustered orders and order_item tables Clustered orders and order_item tables

  7. Cluster Types Index cluster Hash cluster Hash function

  8. Situations Where Clusters Are Useful Criterion Uniform key distribution Evenly distributed key values Rarely updated key Often joined master-detail tables Predictable number of key values Queries using equality predicate on key Index X X X Hash X X X X X

  9. Compositepartitioning Listpartitioning Partitioning Methods • The following partitioning methods are available: • Range • Hash • List • Composite Rangepartitioning Hashpartitioning

  10. Range Partitioning Example CREATE TABLE sales (acct_no NUMBER(5), person VARCHAR2(30), sales_amount NUMBER(8), week_no NUMBER(2)) PARTITION BY RANGE (week_no) (PARTITION P1 VALUES LESS THAN (4) TABLESPACE data0, PARTITION P2 VALUES LESS THAN (8) TABLESPACE data1, ...… PARTITION P13 VALUES LESS THAN (53)TABLESPACE data12 ); 1 3 2 The partition key is week_no. VALUES LESS THAN must be specified as a literal. Physical attributes can be set per partition. 1 2 3

  11. 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

  12. List Partitioning Example SQL> CREATE TABLE locations 2 (location_id, street_address, 3 postal_code, city, state_province, 4 country_id) 5 STORAGE(INITIAL 10K NEXT 20K) 6 TABLESPACE users 7 PARTITION BY LIST (state_province) 8 (PARTITION region_east 9 VALUES('MA','NY','CT','ME','MD'), 10 PARTITION region_west 11 VALUES('CA','AZ','NM','OR','WA'), 12 PARTITION region_south 13 VALUES('TX','KY','TN','LA','MS'), 14 PARTITION region_central 15VALUES('OH','ND','SD','MO','IL'));

  13. Default Partition for List Partitioning • Create a DEFAULT list partition for all values not covered by other partitions: CREATE TABLE customer ... PARTITION BY LIST (state) (PARTITION p1 VALUES ('CA','CO'), PARTITION p2 VALUES ('FL','TX'), PARTITION p3 VALUES (DEFAULT) );

  14. 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

  15. Global Nonpartitioned index Table partition Table partition Table partition Table partition Partitioned Indexes for Scalable Access Global Partitioned Index Local partitioned index

  16. Partition Pruning 99-Jan Partition pruning: Only the relevant partitions are accessed. 99-Feb SQL> SELECT SUM(sales_amount) 2 FROM sales 3 WHERE sales_date BETWEEN 4 TO_DATE(‘01-MAR-1999’, 5 ‘DD-MON-YYYY’) AND 6 TO_DATE(‘31-MAY-1999’, 7 ‘DD-MON-YYYY’); 99-Mar 99-Apr 99-May 99-Jun sales

  17. Partition-Wise Join 3 1 2 Full partition-wise join Nonpartition-wise join Partial partition-wise join Partitioned table Query slave Partition

  18. 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: • Global histograms • Global statistics for indexes

  19. Some dbms_stats Examples CALL dbms_stats.gather_table_stats ( ownname => ‘o901’, tabname => ‘sales’, partname => ‘feb99’, granularity => ‘partition’); CALL dbms_stats.gather_index_stats ( ownname => ‘o901’, indname => ‘isales’, partname => ‘s1’);

  20. Summary • In this lesson, you should have learned how to do the following: • Compare and evaluate the different storage structures • Examine different data access methods • Implement different partitioning methods

More Related