我们知道Oracle支持hash+list二级分区

Oracle环境：

SQL>   create table T_COMPLEX1  (
  2          ID NUMBER,
  3          TIME_ID DATE
  4      )
  5      partition by list (TIME_ID)
  6      SUBPARTITION BY HASH (ID)
  7      (
  8          partition k1 VALUES (TO_DATE('02-12-2002','dd-mm-yyyy')),
  9          partition k2 VALUES (TO_DATE('17-05-2003','dd-mm-yyyy')),
 10          partition k3 VALUES (TO_DATE('15-11-2004','dd-mm-yyyy')),
 11          partition k4 VALUES (TO_DATE('12-04-2006','dd-mm-yyyy'))
 12  ); 

Table created.

lightdb@test=# select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,PARTITION_POSITION from dba_tab_partitions where table_name = 'T_COMPLEX1';
 table_owner | table_name | partition_name | partition_position 
-------------+------------+----------------+--------------------
 PUBLIC      | T_COMPLEX1 | K1             |                  1
 PUBLIC      | T_COMPLEX1 | K2             |                  2
 PUBLIC      | T_COMPLEX1 | K3             |                  3
 PUBLIC      | T_COMPLEX1 | K4             |                  4
(4 rows)

LightDB23.3 Oracle模式：

当LightDB的数据库为Oracle模式时

lightdb@mydb=# show %compatible_type%;
                  name                  | setting |                                   description                                
   
----------------------------------------+---------+---------------------------------------------------------------------------------
 lightdb_dblevel_syntax_compatible_type | Oracle   | Show syntax compatible type for current database.
 lightdb_syntax_compatible_type         | Oracle  | Default syntax compatible type when create database.  Support MySQL and Oracle.

LightDB List+hash 分区表语法

lightdb@test=#   create table T_COMPLEX1  (
lightdb@test(#         ID NUMBER,
lightdb@test(#         TIME_ID DATE
lightdb@test(#     )
lightdb@test-#     partition by list (TIME_ID)
lightdb@test-#     SUBPARTITION BY HASH (ID)
lightdb@test-#     (
lightdb@test(#         partition k1 VALUES (TO_DATE('02-12-2002','dd-mm-yyyy')),
lightdb@test(#         partition k2 VALUES (TO_DATE('17-05-2003','dd-mm-yyyy')),
lightdb@test(#         partition k3 VALUES (TO_DATE('15-11-2004','dd-mm-yyyy')),
lightdb@test(#         partition k4 VALUES (TO_DATE('12-04-2006','dd-mm-yyyy'))
lightdb@test(# ); 
CREATE TABLE

lightdb@test=# create table tmp_part_subpart1
lightdb@test-# (  c0 number not null , 
lightdb@test(#    c1 number not null ,
lightdb@test(#    c2 number not null 
lightdb@test(# ) 
lightdb@test-# partition by list (c0) 
lightdb@test-# subpartition by hash (c1) 
lightdb@test-# subpartitions 4
lightdb@test-# (
lightdb@test(#   partition p1 values (1), 
lightdb@test(#   partition p2 values (2)
lightdb@test(# );
CREATE TABLE

lightdb@test=# select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,PARTITION_POSITION from dba_tab_partitions where table_name = 'T_COMPLEX1';
 table_owner | table_name | partition_name | partition_position 
-------------+------------+----------------+--------------------
 PUBLIC      | T_COMPLEX1 | K1             |                  1
 PUBLIC      | T_COMPLEX1 | K2             |                  2
 PUBLIC      | T_COMPLEX1 | K3             |                  3
 PUBLIC      | T_COMPLEX1 | K4             |                  4
(4 rows)