Oracle 表分区

范围分区（Range）

创建表空间

CREATE SMALLFILE TABLESPACE tablespace_01 DATAFILE
    'D:\Database\Oracle\oradata\finance_dev\tablespace_01.ora' SIZE 10 M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

CREATE SMALLFILE TABLESPACE tablespace_02 DATAFILE
    'D:\Database\Oracle\oradata\finance_dev\tablespace_02.ora' SIZE 10 M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

创建表分区

这里是按照日期进行分区的（按年月日），当然也可以按照主键分区（按数量）

CREATE TABLE range_test (
    id         INTEGER PRIMARY KEY,
    add_time   DATE
)
    PARTITION BY RANGE ( add_time ) ( PARTITION table_01
        VALUES LESS THAN ( TO_DATE('2016-01-01','yyyy-mm-dd') )
        TABLESPACE tablespace_01,
    PARTITION table_02
        VALUES LESS THAN ( TO_DATE('2017-01-01','yyyy-mm-dd') )
        TABLESPACE tablespace_02,
    PARTITION table_01
        VALUES LESS THAN ( MAXVALUE )
        TABLESPACE tablespace_01
    );

测试范围分区

INSERT INTO range_test VALUES (
    1,
    TO_DATE('2016-06-18','yyyy-mm-dd')
);

INSERT INTO range_test VALUES (
    2,
    TO_DATE('2017-08-28','yyyy-mm-dd')
);

INSERT INTO range_test VALUES (
    3,
    TO_DATE('2017-10-10','yyyy-mm-dd')
);

COMMIT;

SET SERVEROUTPUT ON;

DECLARE
    v_sql   VARCHAR2(100);
    v_num   NUMBER;
BEGIN
    IF
        SYSDATE > TO_DATE('2017-01-01','yyyy-mm-dd')
    THEN
        v_sql := 'SELECT count(*) FROM RANGE_TEST PARTITION (table_03)';
    ELSE
        v_sql := 'SELECT count(*) FROM RANGE_TEST PARTITION (table_02)';
    END IF;

    EXECUTE IMMEDIATE v_sql INTO
        v_num;
    dbms_output.put_line(v_num);
END;

散列分区（Hash）

创建表分区

CREATE TABLE hash_test (
    id         INTEGER PRIMARY KEY,
    add_time   DATE
)
    PARTITION BY HASH ( id ) ( 
        PARTITION table_01 TABLESPACE tablespace_01,
        PARTITION table_02 TABLESPACE tablespace_02 
    );

或者是

CREATE TABLE hash_test (
    id         INTEGER PRIMARY KEY,
    add_time   DATE
)
    PARTITION BY HASH ( id ) PARTITIONS 2 STORE IN ( tablespace_01, tablespace_02 );

测试散列分区

INSERT INTO hash_test VALUES (
    1,
    TO_DATE('2016-06-18','yyyy-mm-dd')
);

INSERT INTO hash_test VALUES (
    2,
    TO_DATE('2017-08-28','yyyy-mm-dd')
);

INSERT INTO hash_test VALUES (
    11,
    TO_DATE('2017-10-10','yyyy-mm-dd')
);

COMMIT;

SELECT * FROM HASH_TEST;
SELECT * FROM HASH_TEST PARTITION (table_01);
SELECT * FROM HASH_TEST PARTITION (table_02);

列表分区（List）

创建表分区

CREATE TABLE list_test (
    id             INTEGER PRIMARY KEY,
    finance_type_code   VARCHAR2(3 CHAR),
    add_time       DATE
)
    PARTITION BY LIST ( finance_type_code ) ( 
        PARTITION yfk VALUES ( 'YFK' ),
        PARTITION ddr VALUES ( 'DDR' ),
        PARTITION chr VALUES ( 'CHR' )
    );

测试列表分区

INSERT INTO list_test VALUES (
    1,
    'YFK',
    TO_DATE('2016-06-18','yyyy-mm-dd')
);

INSERT INTO list_test VALUES (
    2,
    'YFK',
    TO_DATE('2016-06-18','yyyy-mm-dd')
);

INSERT INTO list_test VALUES (
    3,
    'DDR',
    TO_DATE('2016-06-18','yyyy-mm-dd')
);

INSERT INTO list_test VALUES (
    4,
    'CHR',
    TO_DATE('2016-06-18','yyyy-mm-dd')
);

COMMIT;

SELECT * FROM LIST_TEST;
SELECT * FROM LIST_TEST PARTITION (yfk);
SELECT * FROM LIST_TEST PARTITION (ddr);
SELECT * FROM LIST_TEST PARTITION (chr);

组合分区

创建表分区

CREATE TABLE group_test (
    id         INTEGER PRIMARY KEY,
    add_time   DATE
)
    PARTITION BY RANGE ( add_time )
        SUBPARTITION BY HASH ( id ) SUBPARTITIONS 2 STORE IN ( tablespace_01, tablespace_02 )
    ( 
        PARTITION Y2017
            VALUES LESS THAN ( TO_DATE('2017-01-01','yyyy-mm-dd') )
            TABLESPACE tablespace_01,
        PARTITION Y2018
            VALUES LESS THAN ( TO_DATE('2018-01-01','yyyy-mm-dd') )
            TABLESPACE tablespace_02,
        PARTITION nowadays
            VALUES LESS THAN ( MAXVALUE )
            TABLESPACE tablespace_02
    );

测试组合分区

INSERT INTO group_test VALUES (
    1,
    TO_DATE('2017-06-18','yyyy-mm-dd')
);

INSERT INTO group_test VALUES (
    2,
    TO_DATE('2016-06-18','yyyy-mm-dd')
);

INSERT INTO group_test VALUES (
    3,
    TO_DATE('2018-06-18','yyyy-mm-dd')
);

INSERT INTO group_test VALUES (
    4,
    TO_DATE('2017-12-18','yyyy-mm-dd')
);

COMMIT;

SELECT * FROM group_test;
SELECT * FROM group_test PARTITION (Y2017);
SELECT * FROM group_test PARTITION (Y2018);
SELECT * FROM group_test PARTITION (nowadays);