partition examples

• range_partitioning
• hash_partitioning
• list_partitioning
• composite_partitioning

[@more@]

Range Partitioning Example
The sales table in the sample schema sh is partitioned by range. The following example shows

an abbreviated variation of the sales table (constraints and storage elements have been

omitted from the example):

CREATE TABLE range_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')),
PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE))
;

For information about partitioned table maintenance operations, see the Oracle9i Database

Administrator's Guide.

List Partitioning Example
The following statement shows how the sample table oe.customers might have been created as a

list-partitioned table (some columns and all constraints of the sample table have been

omitted in this example):

CREATE TABLE list_customers
( customer_id NUMBER(6)
, cust_first_name VARCHAR2(20)
, cust_last_name VARCHAR2(20)
, cust_address CUST_ADDRESS_TYP
, nls_territory VARCHAR2(30)
, cust_email VARCHAR2(30))
PARTITION BY LIST (nls_territory) (
PARTITION asia VALUES ('CHINA', 'THAILAND'),
PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'),
PARTITION west VALUES ('AMERICA'),
PARTITION east VALUES ('INDIA'),
PARTITION rest VALUES (DEFAULT));

Partitioned Table with LOB Columns Example
This statement creates a partitioned table part_tab with two partitions p1 and p2, and three

LOB columns, b, c, and d. The statement uses the sample table pm.print_media, but the LONG

column press_release is omitted because LONG columns are not supported in partitioning.

CREATE TABLE print_media_demo
( product_id NUMBER(6)
, ad_id NUMBER(6)
, ad_composite BLOB
, ad_sourcetext CLOB
, ad_finaltext CLOB
, ad_fltextn NCLOB
, ad_textdocs_ntab textdoc_tab
, ad_photo BLOB
, ad_graphic BFILE
, ad_header adheader_typ
) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab_demo
LOB (ad_composite, ad_photo, ad_finaltext)
STORE AS(STORAGE (NEXT 20M))
PARTITION BY RANGE (product_id)
(PARTITION p1 VALUES LESS THAN (3000) TABLESPACE tbs_1
LOB (ad_composite, ad_photo)
STORE AS (TABLESPACE tbs_2 STORAGE (INITIAL 10M)),
PARTITION P2 VALUES LESS THAN (MAXVALUE)
LOB (ad_composite, ad_finaltext)
STORE AS (TABLESPACE tbs_3)
)
TABLESPACE tbs_4;

Partition p1 will be in tablespace tbs_1. The LOB data partitions for ad_composite and

ad_finaltext will be in tablespace tbs_2. The LOB data partition for ad_photo will be in

tablespace tbs_1. The storage attribute INITIAL is specified for LOB columns ad_composite

and ad_finaltext. Other attributes will be inherited from the default table-level

specification. The default LOB storage attributes not specified at the table level will be

inherited from the tablespace tbs_2 for columns ad_composite and ad_finaltext and tablespace

tbs_1 for column ad_photo. LOB index partitions will be in the same tablespaces as the

corresponding LOB data partitions. Other storage attributes will be based on values of the

corresponding attributes of the LOB data partitions and default attributes of the tablespace

where the index partitions reside.

Partition p2 will be in the default tablespace tbs_4. The LOB data for ad_composite and

ad_photo will be in tablespace tbs_3. The LOB data for ad_finaltext will be in tablespace

tbs_4. The LOB index for columns ad_composite and ad_photo will be in tablespace tbs_3. The

LOB index for column ad_finaltext will be in tablespace tbs_4.

Hash Partitioning Example
The sample table oe.product_information is not partitioned. However, you might want to

partition such a large table by hash for performance reasons, as shown in this example. (The

tablespace names are hypothetical in this example.)

CREATE TABLE hash_products
( product_id NUMBER(6)
, product_name VARCHAR2(50)
, product_description VARCHAR2(2000)
, category_id NUMBER(2)
, weight_class NUMBER(1)
, warranty_period INTERVAL YEAR TO MONTH
, supplier_id NUMBER(6)
, product_status VARCHAR2(20)
, list_price NUMBER(8,2)
, min_price NUMBER(8,2)
, catalog_url VARCHAR2(50)
, CONSTRAINT product_status_lov
CHECK (product_status in ('orderable'
,'planned'
,'under development'
,'obsolete')
) )
PARTITION BY HASH (product_id)
PARTITIONS 5
STORE IN (tbs_1, tbs_2, tbs_3, tbs_4);

Composite-Partitioned Table Examples
The table created in the "Range Partitioning Example" divides data by time of sale. If you

plan to access recent data according to distribution channel as well as time, then composite

partitioning might be more appropriate. The following example creates a copy of that

range_sales table, but with range-hash composite partitioning. The partitions with the most

recent data are subpartitioned with both Oracle-defined and user-defined subpartition names.

(Constraints and storage attributes have been omitted from the example).

CREATE TABLE composite_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
SUBPARTITION BY HASH (channel_id)
(PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY'))
SUBPARTITIONS 8,
PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY'))
(SUBPARTITION ch_c,
SUBPARTITION ch_i,
SUBPARTITION ch_p,
SUBPARTITION ch_s,
SUBPARTITION ch_t),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE)
SUBPARTITIONS 4)
;

The following examples creates a partitioned table of customers based on the sample table

oe.customers. In this example, the table is partitioned on the credit_limit column and list

subpartitioned on the nls_territory column. The subpartition template determines the

subpartitioning of any subsequently added partitions (unless you override the template by

defining individual subpartitions). This composite partitioning makes it possible to query

the table based on a credit limit range within a specified region:

CREATE TABLE customers_part (
customer_id NUMBER(6),
cust_first_name VARCHAR2(20),
cust_last_name VARCHAR2(20),
nls_territory VARCHAR2(30),
credit_limit NUMBER(9,2))
PARTITION BY RANGE (credit_limit)
SUBPARTITION BY LIST (nls_territory)
SUBPARTITION TEMPLATE
(SUBPARTITION east VALUES
('CHINA', 'JAPAN', 'INDIA', 'THAILAND'),
SUBPARTITION west VALUES
('AMERICA', 'GERMANY', 'ITALY', 'SWITZERLAND'),
SUBPARTITION other VALUES (DEFAULT))
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/79291/viewspace-916711/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/79291/viewspace-916711/