分割槽表概念 partitioning table

1)、簡單分割槽

range

list

hash

2）、複合分割槽

range list

range hash

example：

1）、list partition

create table aaa_partitioned
(
id number,
xm varchar2(100),
location varchar2(100),
note varchar2(100),
constraint pk_aaa_partitioned primary key (id)
)

PARTITION BY LIST (location) (
PARTITION aaa_partitioned_beijing VALUES ('beijing'),
PARTITION aaa_partitioned_shanghai VALUES ('shanghai'),
PARTITION aaa_partitioned_tianjin VALUES ('tianjin'),
PARTITION aaa_partitioned_chongqing VALUES ('chongqing'),
PARTITION aaa_partitioned_jinan VALUES (DEFAULT));

2)、range partition

CREATE TABLE aaa_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-04-1998','DD-MM-YYYY')),
PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-07-1998','DD-MM-YYYY')),
PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-10-1998','DD-MM-YYYY')),
PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-01-1999','DD-MM-YYYY')),
PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-04-1999','DD-MM-YYYY')),
PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-07-1999','DD-MM-YYYY')),
PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-10-1999','DD-MM-YYYY')),
PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-01-2000','DD-MM-YYYY')),
PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-04-2000','DD-MM-YYYY')),
PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-07-2000','DD-MM-YYYY')),
PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-10-2000','DD-MM-YYYY')),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE))
;

增加一個分割槽：

ALTER TABLE aaa_range_sales split PARTITION SALES_Q4_2000 at (TO_DATE('01-01-2001','DD-MM-YYYY')) into (PARTITION SALES_Q4_2000,PARTITION SALES_Q4_2000b)

3）、hash分割槽

CREATE TABLE aaa_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)
( PARTITION partition1, PARTITION partition2,PARTITION partition3,PARTITION partition4,PARTITION partition5)

4）、range list分割槽

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));

5）、range hash分割槽

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)
;

hash分割槽名稱，可以自定義，也可以系統產生

分割槽表注意事項：

1、分割槽表可以跨tablespace，建議跨tablespace提高io效能

2、tablespace必須是同樣大小的db_block_size

3、索引：local global之分

4、根據需要可以分割槽可以拆分或者合併。split/merge

5、操作具體的分割槽 同 操作具體的table有n多相似

[@more@]