gpcontrib/orafce/sql/gp_partition_by.sql

-- test partition by list
CREATE TABLE t3 (
    str oracle.varchar2(30)
) PARTITION BY LIST (str)
(
    PARTITION t3_1 VALUES ('a', 'b'),
    PARTITION t3_2 VALUES ('c')
);
INSERT INTO t3 VALUES ('a'), ('b'), ('c');
ANALYZE t3;
SELECT * FROM t3 ORDER BY str;
\d+ t3

CREATE TABLE t4 (
    str oracle.nvarchar2(30)
) PARTITION BY LIST (str)
(
    PARTITION t4_1 VALUES ('a', 'b'),
    PARTITION t4_2 VALUES ('c')
);
INSERT INTO t4 VALUES ('a'), ('b'), ('c');
ANALYZE t4;
SELECT * FROM t4 ORDER BY str;
\d+ t4

-- test partition by hash
CREATE TABLE t5 (
    str oracle.varchar2(30)
) PARTITION BY HASH (str);
CREATE TABLE t5_1 PARTITION OF t5 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE t5_2 PARTITION OF t5 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE t5_3 PARTITION OF t5 FOR VALUES WITH (MODULUS 3, REMAINDER 2);
INSERT INTO t5 VALUES ('a'), ('b'), ('c');
SELECT * FROM t5_1 ORDER BY str;
SELECT * FROM t5_2 ORDER BY str;
SELECT * FROM t5_3 ORDER BY str;
\d+ t5

CREATE TABLE t6 (
    str oracle.nvarchar2(30)
) PARTITION BY HASH (str);
CREATE TABLE t6_1 PARTITION OF t6 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE t6_2 PARTITION OF t6 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE t6_3 PARTITION OF t6 FOR VALUES WITH (MODULUS 3, REMAINDER 2);
INSERT INTO t6 VALUES ('a'), ('b'), ('c');
SELECT * FROM t6_1 ORDER BY str;
SELECT * FROM t6_2 ORDER BY str;
SELECT * FROM t6_3 ORDER BY str;
\d+ t6

DROP TABLE t3;
DROP TABLE t4;
DROP TABLE t5;
DROP TABLE t6;