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多相似