[Oracle] 分区表

Range Partitioning

CREATE TABLE sales_range

(

    salesman_id  NUMBER(5),

    salesman_name VARCHAR2(30),

    sales_amount  NUMBER(10),

    sales_date    DATE)

    PARTITION BY RANGE(sales_date)

    (

        PARTITION sales_jan2000 VALUES LESS THAN('02/01/2000'),

        PARTITION sales_feb2000 VALUES LESS THAN('03/01/2000'),

        PARTITION sales_mar2000 VALUES LESS THAN('04/01/2000'),

        PARTITION sales_apr2000 VALUES LESS THAN('05/01/2000')

        PARTITION sales_2000 VALUES LESS THAN(MAXVALUE))

);

 

List Partitioning

CREATE TABLE sales_list
(

    salesman_id  NUMBER(5),
    salesman_name VARCHAR2(30),
    sales_state   VARCHAR2(20),
    sales_amount  NUMBER(10),
    sales_date    DATE)
    PARTITION BY LIST(sales_state)
    (
        PARTITION sales_west VALUES('California', 'Hawaii'),
        PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
        PARTITION sales_central VALUES('Texas', 'Illinois'),
        PARTITION sales_other VALUES(DEFAULT))
);

Hash Partitioning
分区数据最好是２的幂，这样可以平均分配数据。
CREATE TABLE sales_hash
(

    salesman_id  NUMBER(5),
    salesman_name VARCHAR2(30),
    sales_amount  NUMBER(10),
    week_no       NUMBER(2))
    PARTITION BY HASH(salesman_id)
    (
        PARTITION p1 tablespace users,
        PARTITION p2 tablespace system)
);

分区维护操作

移动分区
Alter table sales_hash move partition p2 tablespace users;

添加分区
Alter table t add partition p3 values less than……
只能在已经分区表的最后一个分区之后添加，并且最后一个分区使用特定健值定义，不能是maxvalue.
如果想在中间或开始部分，或者maxvalue后，添加分区，使用split 分裂已有分区。

拆分分区
ALTER TABLE SALES_RANGE SPLIT PARTITION sales_2000
at (TO_DATE('01/05/2001','DD/MM/YYYY'))
INTO ( PARTITION sales_2000_1, PARTITION sales_2000_2);

相当于：PARTITION sales_2000_1 values less than(TO_DATE('01/05/2001','DD/MM/YYYY'))

删除分区
Alter table t drop partition p3;
Alter table t truncate partition p3;

合并分区
ALTER TABLE four_seasons
MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two;

交换表分区
CREATE TABLE sales_range_temp
(   salesman_id  NUMBER(5),
    salesman_name VARCHAR2(30),
    sales_amount  NUMBER(10),
    sales_date    DATE);

insert into sales_range_temp values(11,11,11,sysdate);

select * from sales_range partition(sales_2000_2);

alter table sales_range exchange partition sales_2000 with table sales_range_temp;

 

Partition.jpg

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

转载于:http://blog.itpub.net/58054/viewspace-624852/