ORD_NO -----101 102
ORD_DT CUST_CD ----------05-JAN-97 R01 07-JAN-97 N45
Unclustered orders and order_item tables
Clustered orders and order_item tables
14-7
14-16
Copyright © Oracle Corporation, 2002. All rights reserved.
Partitioned Indexes for Scalable Access
Global Nonpartitioned index Global Partitioned Index
Partitioned table
14-3
Copyright © Oracle Corporation, 2002. All rights reserved.
Selecting the Physical Structure
Factors affecting the selection: • Rows read in groups • SELECT or DML statements
Copyright © Oracle Corporation, 2002. All rights reserved.
Cluster Types
Index cluster Hash clusterLeabharlann Hash function
14-8
Copyright © Oracle Corporation, 2002. All rights reserved.
99-Jan
99-Feb 99-Mar 99-Apr 99-May 99-Jun
Partition pruning: Only the relevant partitions are accessed.
SQL> 2 3 4 5 6 7 SELECT SUM(sales_amount) FROM sales WHERE sales_date BETWEEN TO_DATE(‘01-MAR-1999’, ‘DD-MON-YYYY’) AND TO_DATE(‘31-MAY-1999’, ‘DD-MON-YYYY’);
Predictable number of key values Queries using equality predicate on key
X
X X
14-9
Copyright © Oracle Corporation, 2002. All rights reserved.
Partitioning Methods
CREATE TABLE customer ... PARTITION BY LIST (state) (PARTITION p1 VALUES ('CA','CO'), PARTITION p2 VALUES ('FL','TX'), PARTITION p3 VALUES (DEFAULT) );
14-6
Copyright © Oracle Corporation, 2002. All rights reserved.
Clusters
ORD_NO ----101 102 102 102 101 101 PROD -----A4102 A2091 G7830 N9587 A5675 W0824 QTY -----20 11 20 26 19 10 ... 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
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
Table partition
Table partition
Table partition
Table partition
Local partitioned index
14-17 Copyright © Oracle Corporation, 2002. All rights reserved.
Partition Pruning
14-14 Copyright © Oracle Corporation, 2002. All rights reserved.
Default Partition for List Partitioning
Create a DEFAULT list partition for all values not covered by other partitions:
The following partitioning methods are available: • Range • Hash • List • Composite
Range partitioning
14-10
Hash partitioning
List partitioning
Composite partitioning
• • • • Table size Row size, row group, and block size Small or large transactions Using parallel queries to load or for SELECT statements
14-4
Copyright © Oracle Corporation, 2002. All rights reserved.
The partition key is week_no. 2 VALUES LESS THAN must be specified as a literal. 3 Physical attributes can be set per partition.
1
14-12
Copyright © Oracle Corporation, 2002. All rights reserved.
14-2
Copyright © Oracle Corporation, 2002. All rights reserved.
Data Storage Structures
Heap table
Cluster
Indexorganized table
Organization by value Heap Clustered Sorted
Copyright © Oracle Corporation, 2002. All rights reserved.
Range Partitioning Example
CREATE TABLE sales (acct_no NUMBER(5), person VARCHAR2(30), sales_amount NUMBER(8), 1 week_no NUMBER(2)) 3 PARTITION BY RANGE (week_no) 2 (PARTITION P1 VALUES LESS THAN (4) TABLESPACE data0, PARTITION P2 VALUES LESS THAN (8) TABLESPACE data1, ...… PARTITION P13 VALUES LESS THAN (53)TABLESPACE data12 );
14-13
Copyright © Oracle Corporation, 2002. All rights reserved.
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 15 VALUES('OH','ND','SD','MO','IL'));
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