GBase 8c Compatibility - Oracle Partition Syntax

GBase 8c Distributed Edition supports Oracle-compatible partition table functionality, including two-level partitions, nine partition combinations, and interval partitions:

Hash Partition Example

The SQL syntax for hash partitioning is identical to Oracle.

Single-level hash partition:

DROP TABLE IF EXISTS mea_hash CASCADE;
CREATE TABLE mea_hash (
    city_id INT,
    logdate TIMESTAMP,
    id INT
) PARTITION BY HASH(id) (
    PARTITION p1,
    PARTITION p2
);

Two-level partitions: hash-list, hash-hash, hash-range

DROP TABLE IF EXISTS mea_hash_list CASCADE;
CREATE TABLE mea_hash_list (
    city_id INT,
    logdate TIMESTAMP,
    id INT
) PARTITION BY HASH(id) SUBPARTITION BY LIST(city_id) (
    PARTITION p1 (
        SUBPARTITION p12 VALUES (10),
        SUBPARTITION p13 VALUES (20)
    )
);

DROP TABLE IF EXISTS mea_hash_hash CASCADE;
CREATE TABLE mea_hash_hash (
    city_id INT,
    logdate TIMESTAMP,
    id INT
) PARTITION BY HASH(id) SUBPARTITION BY HASH(city_id) (
    PARTITION id_1 (
        SUBPARTITION p12,
        SUBPARTITION p13
    )
);

DROP TABLE IF EXISTS mea_hash_range CASCADE;
CREATE TABLE mea_hash_range (
    city_id INT,
    logdate TIMESTAMP,
    id INT
) PARTITION BY HASH(id) SUBPARTITION BY RANGE(logdate) (
    PARTITION meas_y2021 (
        SUBPARTITION p12 VALUES LESS THAN ('2021-02-04 12:00:00'),
        SUBPARTITION p13 VALUES LESS THAN ('2021-02-04 20:00:00')
    )
);

Range Partition Example

The SQL syntax for range partitioning is identical to Oracle.

DROP TABLE IF EXISTS mea_range CASCADE;
CREATE TABLE mea_range (
    city_id INT,
    logdate TIMESTAMP
) PARTITION BY RANGE(logdate) (
    PARTITION meas_y2021 VALUES LESS THAN ('2021-01-01')
);

Two-level partitions: range-range, range-hash, range-list

DROP TABLE IF EXISTS mea_range_range CASCADE;
CREATE TABLE mea_range_range (
    city_id INT,
    logdate TIMESTAMP,
    id INT
) PARTITION BY RANGE(logdate) SUBPARTITION BY RANGE(id) (
    PARTITION meas_y2021 VALUES LESS THAN ('2021-02-04 21:00:00') (
        SUBPARTITION p12 VALUES LESS THAN (1),
        SUBPARTITION p13 VALUES LESS THAN (10)
    )
);

DROP TABLE IF EXISTS mea_range_hash CASCADE;
CREATE TABLE mea_range_hash (
    city_id INT,
    logdate TIMESTAMP,
    id INT
) PARTITION BY RANGE(logdate) SUBPARTITION BY HASH(city_id) (
    PARTITION id_1 VALUES LESS THAN ('2021-02-01 01:00:00') (
        SUBPARTITION p12,
        SUBPARTITION p13
    )
);

DROP TABLE IF EXISTS mea_range_list CASCADE;
CREATE TABLE mea_range_list (
    city_id INT,
    logdate TIMESTAMP,
    id INT
) PARTITION BY RANGE(logdate) SUBPARTITION BY LIST(city_id) (
    PARTITION p1 VALUES LESS THAN ('2021-02-01 01:00:00') (
        SUBPARTITION p12 VALUES (1),
        SUBPARTITION p13 VALUES (20)
    )
);

List Partition Example

The SQL syntax for list partitioning is identical to Oracle.

DROP TABLE IF EXISTS mea_list CASCADE;
CREATE TABLE mea_list (
    city_id INT,
    logdate TIMESTAMP,
    id INT
) PARTITION BY LIST(id) (
    PARTITION p1 VALUES (1),
    PARTITION p2 VALUES (2)
);

Two-level partitions: list-list, list-range, list-hash

DROP TABLE IF EXISTS mea_list_list CASCADE;
CREATE TABLE mea_list_list (
    city_id INT,
    logdate TIMESTAMP,
    id INT
) PARTITION BY LIST(id) SUBPARTITION BY LIST(city_id) (
    PARTITION p1 VALUES (1) (
        SUBPARTITION p12 VALUES (10),
        SUBPARTITION p13 VALUES (20)
    )
);

DROP TABLE IF EXISTS mea_list_range CASCADE;
CREATE TABLE mea_list_range (
    city_id INT,
    logdate TIMESTAMP,
    id INT
) PARTITION BY LIST(id) SUBPARTITION BY RANGE(logdate) (
    PARTITION meas_y2021 VALUES ('202102') (
        SUBPARTITION p12 VALUES LESS THAN ('2021-02-04 12:00:00'),
        SUBPARTITION p13 VALUES LESS THAN ('2021-02-04 20:00:00')
    )
);

DROP TABLE IF EXISTS mea_list_hash CASCADE;
CREATE TABLE mea_list_hash (
    city_id INT,
    logdate TIMESTAMP,
    id INT
) PARTITION BY LIST(id) SUBPARTITION BY HASH(city_id) (
    PARTITION id_1 VALUES (2021) (
        SUBPARTITION p12,
        SUBPARTITION p13
    )
);

Additional Partition Syntax

GBase 8c also supports Oracle's new partition syntax such as dropping subpartitions, renaming, and splitting subpartitions.

Create a partition table with integer partition keys:

CREATE TABLE tpcds.startend_pt (
    c1 INT,
    c2 INT
) TABLESPACE startend_tbs1 
PARTITION BY RANGE (c2) (
    PARTITION p1 START(1) END(1000) EVERY(200) TABLESPACE startend_tbs2,
    PARTITION p2 END(2000),
    PARTITION p3 START(2000) END(2500) TABLESPACE startend_tbs3,
    PARTITION p4 START(2500),
    PARTITION p5 START(3000) END(5000) EVERY(1000) TABLESPACE startend_tbs4
) ENABLE ROW MOVEMENT;

View partition table information:

SELECT relname, boundaries, spcname 
FROM pg_partition p 
JOIN pg_tablespace t ON p.reltablespace=t.oid 
AND p.parentid='tpcds.startend_pt'::regclass 
ORDER BY 1;

Insert data and view partition data volume:

INSERT INTO tpcds.startend_pt VALUES (GENERATE_SERIES(0, 4999), GENERATE_SERIES(0, 4999));
SELECT COUNT(*) FROM tpcds.startend_pt PARTITION FOR (0);

SELECT COUNT(*) FROM tpcds.startend_pt PARTITION (p3);

Add partitions:

ALTER TABLE tpcds.startend_pt ADD PARTITION p6 START(5000) END(6000) EVERY(300) TABLESPACE startend_tbs4;
ALTER TABLE tpcds.startend_pt ADD PARTITION p7 END(MAXVALUE);
ALTER TABLE tpcds.startend_pt RENAME PARTITION p7 TO p8;
ALTER TABLE tpcds.startend_pt DROP PARTITION p8;
ALTER TABLE tpcds.startend_pt RENAME PARTITION FOR(5950) TO p71;
ALTER TABLE tpcds.startend_pt SPLIT PARTITION FOR(4500) INTO(PARTITION q1 START(4000) END(5000) EVERY(250) TABLESPACE startend_tbs3);
ALTER TABLE tpcds.startend_pt MOVE PARTITION p2 TABLESPACE startend_tbs4;

Blog

GBase 8c Compatibility - Oracle Partition Syntax

Cong Li

Hash Partition Example

Range Partition Example

List Partition Example

Additional Partition Syntax

Join Our Newsletter. No Spam, Only the good stuff.

Related