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;
Top comments (0)