lightdb创建oracle兼容分区表

zjh@postgres=# create table hash_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
zjh@postgres-# partition by hash (deal_date)
zjh@postgres-# PARTITIONS 12;
ERROR:  please set oracle compatible mode for oracle partition!
LINE 2: partition by hash (deal_date)
        ^
zjh@postgres=# \c 
lt_test    oradb      postgres   template0  template1  test1      
zjh@postgres=# \c test1 
You are now connected to database "test1" as user "zjh".
zjh@test1=# create table hash_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
zjh@test1-# partition by hash (deal_date)
zjh@test1-# PARTITIONS 12
zjh@test1-# ;
CREATE TABLE

zjh@test1=# create table t_hash_partition(a int,b int) partition by hash(a)  (partition p1 tablespace pg_default,partition p2 tablespace pg_default);
CREATE TABLE

zjh@test1=# \dS+ t_hash_partition
                       Partitioned table "public.t_hash_partition"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 a      | integer |           |          |         | plain   |              | 
 b      | integer |           |          |         | plain   |              | 
Partition key: HASH (a)
Partitions: t_hash_partition_1_prt_p1 FOR VALUES WITH (modulus 2, remainder 0),
            t_hash_partition_1_prt_p2 FOR VALUES WITH (modulus 2, remainder 1)

zjh@test1=# \dS+ hash_part_tab
                              Partitioned table "public.hash_part_tab"
  Column   |      Type      | Collation | Nullable | Default | Storage | Stats target | Description 
-----------+----------------+-----------+----------+---------+---------+--------------+-------------
 id        | numeric        |           |          |         | main    |              | 
 deal_date | date           |           |          |         | plain   |              | 
 area_code | numeric        |           |          |         | main    |              | 
 nbr       | numeric        |           |          |         | main    |              | 
 contents  | varchar2(4000) |           |          |         | plain   |              | 
Partition key: HASH (deal_date)
Partitions: hash_part_tab_1_prt_p0 FOR VALUES WITH (modulus 12, remainder 0),
            hash_part_tab_1_prt_p1 FOR VALUES WITH (modulus 12, remainder 1),
            hash_part_tab_1_prt_p10 FOR VALUES WITH (modulus 12, remainder 10),
            hash_part_tab_1_prt_p11 FOR VALUES WITH (modulus 12, remainder 11),
            hash_part_tab_1_prt_p2 FOR VALUES WITH (modulus 12, remainder 2),
            hash_part_tab_1_prt_p3 FOR VALUES WITH (modulus 12, remainder 3),
            hash_part_tab_1_prt_p4 FOR VALUES WITH (modulus 12, remainder 4),
            hash_part_tab_1_prt_p5 FOR VALUES WITH (modulus 12, remainder 5),
            hash_part_tab_1_prt_p6 FOR VALUES WITH (modulus 12, remainder 6),
            hash_part_tab_1_prt_p7 FOR VALUES WITH (modulus 12, remainder 7),
            hash_part_tab_1_prt_p8 FOR VALUES WITH (modulus 12, remainder 8),
            hash_part_tab_1_prt_p9 FOR VALUES WITH (modulus 12, remainder 9)

zjh@test1=# insert into t_hash_partition values(1,1);
INSERT 0 1
zjh@test1=# insert into t_hash_partition values(2,2);
INSERT 0 1
zjh@test1=# insert into t_hash_partition values(3,3);
INSERT 0 1
zjh@test1=# insert into t_hash_partition values(4,4);
INSERT 0 1
zjh@test1=# explain select * from t_hash_partition where a = 1;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Seq Scan on t_hash_partition_1_prt_p1 t_hash_partition  (cost=0.00..38.25 rows=11 width=8)
   Filter: (a = 1)
(2 rows)

注：截止23.1版本，lightdb不支持default分区，不支持list+hash组合分区（range+hash代替）。

自动分区支持

参考：

lightdb各种分区操作 https://blog.csdn.net/qq_22066003/article/details/126707631

oracle 11g自动分区 https://www.jianshu.com/p/d0a90db0617c