Updated on 2024-05-07 GMT+08:00

CREATE TABLE SUBPARTITION

Description

Creates a level-2 partitioned table. Partitioning refers to splitting what is logically one large table into smaller physical pieces based on specific schemes. The table based on the logic is called a partitioned table, and each physical piece is called a partition. Data is stored on these physical partitions, instead of the logical partitioned table. For a level-2 partitioned table, the top-level node table and level-1 partitioned table are logical tables and do not store data. Only the level-2 partitioned (leaf node) stores data.

The partitioning solution of a level-2 partitioned table is a combination of the partitioning solutions of two level-1 partitions. For details about the partitioning solution of a level-1 partitioned table, see CREATE TABLE PARTITION.

Common combination solutions for level-2 partitioned tables include range-range partitioning, range-list partitioning, range-hash partitioning, list-range partitioning, list-list partitioning, list-hash partitioning, hash-range partitioning, hash-list partitioning, and hash-hash partitioning. Currently, level-2 partitioned tables can only be row-store tables.

Precautions

  • A level-2 partitioned table has two partition keys, and each partition key supports only one column.
  • If the constraint key of the unique constraint and primary key constraint contains all partition keys, a local index is created for the constraints. Otherwise, a global index is created. If a local unique index is created, all partition keys must be included.
  • When a level-2 partitioned table is created, if the specified level-2 partition is not displayed under the level-1 partition, a level-2 partition with the same range is automatically created.
  • The number of level-2 partitions (leaf nodes) in a level-2 partitioned table cannot exceed 1048575. There is no limit on the number of level-1 partitions, but there must be at least one level-2 partition under a level-1 partition.
  • The maximum total number of partitions (including level-1 and level-2 partitions) in a level-2 partitioned table is 1048575. Generally, it is impossible for services to create so many partitions and the memory will be insufficient if so. Create partitions based on the value of local_syscache_threshold. The memory allocated to the level-2 partitioned tables can be calculated as follows: total number of partitions x 3/1024, in MB. Theoretically, the memory occupied by the partitions cannot be greater than the value of local_syscache_threshold. In addition, some space must be reserved for other functions.
  • If the memory is insufficient due to too many partitions, the performance deteriorates sharply.
  • Level-2 partitioned tables do not support hash bucket.
  • Clusters are not supported.
  • When specifying a partition for query, for example, running SELECT * FROM tablename PARTITION/SUBPARTITION (partitionname), ensure that the keyword PARTITION and SUBPARTITION is correct. If they are incorrect, no error is reported during the query. In this case, the query is performed based on the table alias.
  • It does not support: encrypted database; row-level security.
  • In the PARTITION/SUBPARTITION FOR (values) syntax for level-2 partitioned tables, values can only be constants.
  • In the PARTITION/SUBPARTITION FOR (values) syntax for level-2 partitioned tables, if data type conversion is required for values, you are advised to use forcible type conversion to prevent the implicit type conversion result from being inconsistent with the expected result.
  • Currently, the statement specifying a partition cannot perform global index scan.

Syntax

CREATE TABLE [ IF NOT EXISTS ] subpartition_table_name
( 
{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option [...] ] }[, ... ]
)
[ AUTO_INCREMENT [ = ] value ]
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ COMPRESS | NOCOMPRESS ][ COMPRESS | NOCOMPRESS ]
[ TABLESPACE tablespace_name ]
PARTITION BY {RANGE [ COLUMNS ] | LIST [ COLUMNS ] | HASH | KEY} (partition_key) [ PARTITIONS integer ] SUBPARTITION BY {RANGE | LIST | HASH | KEY} (subpartition_key) [ SUBPARTITIONS integer ]
(
  PARTITION partition_name1 [ VALUES LESS THAN {(val1) | MAXVALUE} | VALUES [IN] (val1[, ...]) ] [ TABLESPACE [=] tablespace ]
  [(
       { SUBPARTITION subpartition_name1 [ VALUES LESS THAN (val1_1) | VALUES (val1_1[, ...])] [ TABLESPACE [=] tablespace ] } [, ...]
  )][, ...]
)[ { ENABLE | DISABLE } ROW MOVEMENT ];
  • Column constraint:
    [ CONSTRAINT constraint_name ]
    { NOT NULL |
      NULL | 
      CHECK ( expression ) | 
      DEFAULT default_e xpr | 
      GENERATED ALWAYS AS ( generation_expr ) [STORED] |
      AUTO_INCREMENT |
      UNIQUE [KEY] index_parameters | 
      PRIMARY KEY index_parameters |
      REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
            [ ON DELETE action ] [ ON UPDATE action ] }
    [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  • Table constraint:
    [ CONSTRAINT [ constraint_name ] ]
    { CHECK ( expression ) | 
      UNIQUE  [ index_name ][ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters | 
      PRIMARY KEY  [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters |
      FOREIGN KEY [ index_name ] ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
          [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
    [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  • LIKE options:
    { INCLUDING | EXCLUDING } { DEFAULTS | GENERATED | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | RELOPTIONS| ALL }
  • Index parameters:
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ USING INDEX TABLESPACE tablespace_name ]

Parameters

  • IF NOT EXISTS

    Does not throw an error if a relationship with the same name existed. A notice is issued in this case.

  • subpartition_table_name

    Specifies the name of a level-2 partitioned table.

    Value range: a string. It must comply with the naming convention.

  • column_name

    Specifies the name of a column to be created in the new table.

    Value range: a string. It must comply with the naming convention.

  • data_type

    Specifies the data type of the column.

  • COLLATE collation

    Assigns a collation to the column (which must be of a collatable data type). If no collation is specified, the default collation is used. You can run the SELECT * FROM pg_collation command to query collation rules from the pg_collation system catalog. The default collation rule is the row starting with default in the query result.

  • CONSTRAINT constraint_name

    Specifies the name of a column or table constraint. The optional constraint clauses specify constraints that new or updated rows must satisfy for an INSERT or UPDATE operation to succeed.

    There are two ways to define constraints:

    • A column constraint is defined as part of a column definition, and it is bound to a particular column.
    • A table constraint applies to multiple columns.

      The constraint_name is optional in B-compatible mode (sql_compatibility = 'B'). For other modes, constraint_name must be added.

  • index_name

    Index name

    • The index_name is supported only in B-compatible databases (that is, sql_compatibility = 'B').
    • For foreign key constraints, if constraint_name and index_name are specified at the same time, constraint_name is used as the index name.
    • For a unique key constraint, if both constraint_name and index_name are specified, index_name is used as the index name.
  • USING method

    Specifies the name of the index method to be used.

    For details about the value range, see USING method in Parameters.

    • The USING method is supported only in B-compatible databases (that is, sql_compatibility = 'B').
    • In B-compatible mode, if USING method is not specified, the default index method is btree for ASTORE or UB-tree for USTORE.
  • ASC | DESC

    ASC specifies an ascending (default) sort order. DESC specifies a descending sort order.

    The ASC|DESC is supported only in B-compatible databases (that is, sql_compatibility = 'B').

  • LIKE source_table [ like_option ... ]

    Specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints. Unlike INHERITS, the new table and original table are decoupled after creation is complete. Changes to the original table will not be applied to the new table, and it is not possible to include data of the new table in scans of the original table.

    • Default expressions for the copied column definitions will be copied only if INCLUDING DEFAULTS is specified. The default behavior is to exclude default expressions, resulting in the copied columns in the new table having default values null.
    • If INCLUDING GENERATED is specified, the generated expression of the original table column is copied to the new table. By default, the generated expression is not copied.
    • Non-null constraints are always copied to the new table. CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never be copied. These rules also apply to column constraints and table constraints.
    • Unlike those of INHERITS, columns and constraints copied by LIKE are not merged with similarly named columns and constraints. If the same name is specified explicitly or in another LIKE clause, an error is reported.
    • Any indexes on the original table will not be created on the new table, unless the INCLUDING INDEXES clause is specified.
    • STORAGE settings for the original columns are copied only if INCLUDING STORAGE is specified. The default behavior is to exclude STORAGE settings.
    • If INCLUDING COMMENTS is specified, comments for the copied original columns, constraints, and indexes are copied. The default behavior is to exclude comments.
    • If INCLUDING RELOPTIONS is specified, the new table will copy the storage parameter (that is, WITH clause) of the original table. The default behavior is to exclude partition definition of the storage parameter of the original table.
    • INCLUDING ALL contains the meaning of INCLUDING DEFAULTS, INCLUDING CONSTRAINTS, INCLUDING INDEXES, INCLUDING STORAGE, INCLUDING COMMENTS, INCLUDING PARTITION, and INCLUDING RELOPTIONS.
  • AUTO_INCREMENT [ = ] value

    This clause specifies an initial value for an auto-increment column. The value must be a positive integer and cannot exceed 2127 – 1.

    This clause takes effect only when sql_compatibility is set to 'B'.

  • WITH ( storage_parameter [= value] [, ... ] )

    Specifies an optional storage parameter for a table or an index. Optional parameters are as follows:

    • FILLFACTOR

      The fill factor of a table is a percentage from 10 to 100. If the Ustore storage engine is used, the default value is 92. If the Astore storage engine is used, the default value is 100 (completely filled). When a smaller fill factor is specified, INSERT operations pack table pages only to the indicated percentage. The remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page, which is more efficient than placing it on a different page. For a table whose entries are never updated, setting the fill factor to 100 (complete filling) is the best choice, but in heavily updated tables a smaller fill factor would be appropriate.

      Value range: 10–100

    • ORIENTATION

      Determines the data storage mode of the table.

      Value range:

      • ROW (default value): The data will be stored in rows.

        ORIENTATION cannot be modified.

    • STORAGE_TYPE

      Specifies the storage engine type. This parameter cannot be modified once it is set.

      Value range:

      • USTORE indicates that tables support the inplace-update storage engine. Note that the track_counts and track_activities parameters must be enabled when the Ustore table is used. Otherwise, space bloating may occur.
      • ASTORE indicates that tables support the append-only storage engine.

      Default value:

      If no table is specified, data is stored in inplace-update mode by default.

    • COMPRESSION
      • Row-store tables do not support compression.
    • segment

      This parameter is reserved and is not supported currently.

  • COMPRESS / NOCOMPRESS

    Specifies keyword COMPRESS during the creation of a table, so that the compression feature is triggered in case of BULK INSERT operations. If this feature is enabled, a scan is performed for all tuple data within the page to generate a dictionary and then the tuple data is compressed and stored. If NOCOMPRESS is specified, the table is not compressed. Row-store tables do not support compression.

    Default value: NOCOMPRESS, that is, tuple data is not compressed before storage.

  • TABLESPACE tablespace_name

    Specifies that the new table will be created in the tablespace_name tablespace. If not specified, the default tablespace is used.

  • PARTITION BY {RANGE [COLUMNS] | LIST [COLUMNS] | HASH | KEY} (partition_key)
    • For partition_key, the partitioning policy supports only one column of partition keys.
    • The data types supported by the partition key are the same as those supported by the level-1 partitioned table.
    • The COLUMNS keyword can be used only when sql_compatibility is set to 'B' and can be added only after RANGE or LIST. The semantics of RANGE COLUMNS is the same as that of RANGE, and the semantics of LIST COLUMNS is the same as that of LIST.
    • The KEY keyword can be used only when sql_compatibility is set to 'B'. The meaning of KEY is the same as that of HASH.
  • SUBPARTITION BY {RANGE | LIST | HASH | KEY} (subpartition_key)
    • For subpartition_key, the partitioning policy supports only one column of partition keys.
    • The data types supported by the partition key are the same as those supported by the level-1 partitioned table.
    • The KEY keyword can be used only when sql_compatibility is set to 'B'. The meaning of KEY is the same as that of HASH.
  • PARTITIONS integer

    Specifies the number of partitions.

    integer indicates the number of partitions. The value must be an integer greater than 0 and cannot be greater than 1048575.

    • When this clause is specified after the range and list partitions, each partition must be explicitly defined, and the number of defined partitions must be equal to the value of integer. This clause can be specified after the range and list partitions only when sql_compatibility is set to 'B'.
    • When this clause is specified after the hash and key partitions, if the definition of each partition is not listed, integer partitions are automatically generated. The automatically generated partition name is "p+number", and the number ranges from 0 to integer minus 1. The tablespace of the partition is the tablespace of the table by default. If each partition definition is explicitly defined, the number of defined partitions must be the same as the value of integer. If neither the partition definition nor the number of partitions is specified, a unique partition is created.
  • SUBPARTITIONS integer

    Specifies the number of level-2 partitions.

    integer indicates the number of level-2 partitions. The value must be an integer greater than 0 and cannot be greater than 1048575.

    • This clause can be specified only for level-2 HASH and KEY partitions.
      • If level-2 partitions are not defined, an integer number of level-2 partitions are automatically generated in each level-1 partition. The automatically generated level-2 partitions are named in the format of "level-1 partition name+sp+number", where the number ranges from 0 to integer minus 1. The tablespace of the partition is the tablespace of the table by default.
      • If level-2 partitions are defined, the number of level-2 partitions must be the same as the value of integer.
      • If level-2 partitions are not defined and the number of level-2 partitions is not specified, a unique level-2 partition is created.
  • { ENABLE | DISABLE } ROW MOVEMENT

    Specifies whether to enable row movement.

    If the tuple value is updated on the partition key during the UPDATE operation, the partition where the tuple is located is altered. Setting this parameter enables error messages to be reported or movement of the tuple between partitions.

    Value range:

    • ENABLE (default value): Row movement is enabled.
    • DISABLE: Row movement is disabled.

    If the row movement is enabled, an error may be reported when update and delete operations are performed concurrently. The causes are as follows:

    The old data is marked as deleted under the update and delete operations. If the row movement is enabled, the cross-partition update occurs when the partition key is updated, the kernel marks the old data in the old partition as deleted and adds a data to the new partition. As a result, the new data cannot be found by querying the old data.

    If data in the same row is concurrently operated, the cross-partition and non-cross-partition data results have different behaviors in the following three concurrency scenarios: UPDATE and UPDATE concurrency, DELETE and DELETE concurrency, UPDATE and DELETE concurrency.

    1. For non-cross-partition data, no error is reported for the second operation after the first operation is performed.
      • If the first operation is UPDATE, the latest data can be found and operated after the second operation is performed.
      • If the first operation is DELETE, the second operation is terminated if the current data is deleted and the latest data cannot be found.
    2. For the cross-partition data result, an error is reported for the second operation after the first operation is performed.
      • If the first operation is UPDATE, the second operation cannot find the latest data because the new data is in the new partition. Therefore, the second operation fails and an error is reported.
      • If the first operation is DELETE, performing the second operation can find that the current data is deleted and the latest data cannot be found, but cannot determine whether the operation of deleting the old data is UPDATE or DELETE. If the operation is UPDATE, an error is reported. If the operation is DELETE, the operation is terminated. To ensure the data correctness, an error is reported.

    If the UPDATE and UPDATE concurrency, and UPDATE and DELETE concurrency are performed, the error can be solved only when the operations are performed serially. If the DELETE and DELETE concurrency are performed, the error can be solved by disabling the row movement.

  • NOT NULL

    The column is not allowed to contain null values. ENABLE can be omitted.

  • NULL

    Indicates that the column is allowed to contain NULL values. This is the default setting.

    This clause is only provided for compatibility with non-standard SQL databases. It is not recommended.

  • CHECK (condition) [ NO INHERIT ]

    Specifies an expression producing a Boolean result where the INSERT or UPDATE operation of new or updated rows can succeed only when the expression result is TRUE or UNKNOWN; otherwise, an error is thrown and the database is not altered.

    A check constraint specified as a column constraint should reference only the column's values, while an expression in a table constraint can reference multiple columns.

    A constraint marked with NO INHERIT will not propagate to child tables.

    ENABLE can be omitted.

  • DEFAULT default_expr

    Assigns a default data value to a column. The value can be any variable-free expressions. (Subqueries and cross-references to other columns in the current table are not allowed.) The data type of the default expression must match that of the column.

    The default expression will be used in any INSERT operation that does not specify a value for the column. If there is no default value for a column, then the default value is NULL.

  • GENERATED ALWAYS AS ( generation_expr ) [STORED]

    This clause creates a column as a generated column. The value of the generated column is calculated by generation_expr when data is written (inserted or updated). STORED indicates that the value of the generated column is stored as a common column.

    • The STORED keyword can be omitted, which has the same semantics as not omitting STORED.
    • The generation expression cannot refer to data other than the current row in any way. The generation expression cannot reference other generation columns or system columns. The generation expression cannot return a result set. No subquery, aggregate function, or window function can be used. The function called by the generation expression can only be an immutable function.
    • Default values cannot be specified for generated columns.
    • The generated column cannot be used as a part of the partition key.
    • Do not specify the generated column and the CASCADE, SET NULL, and SET DEFAULT actions of the ON UPDATE constraint clause together. Do not specify the generated column and the SET NULL, and SET DEFAULT actions of the ON DELETE constraint clause together.
    • The method of modifying and deleting generated columns is the same as that of common columns. Delete the common column that the generated column depends on. The generated column is automatically deleted. The type of the column on which the generated column depends cannot be changed.
    • The generated column cannot be directly written. In the INSERT or UPDATE statement, values cannot be specified for generated columns, but the keyword DEFAULT can be specified.
    • The permission control for generated columns is the same as that for common columns.
  • AUTO_INCREMENT

    Specifies an auto-increment column.

    For details, see AUTO_INCREMENT.

  • UNIQUE [KEY] index_parameters

    UNIQUE ( column_name [, ... ] ) index_parameters

    Specifies that a group of one or more columns of a table can contain only unique values.

    For the purpose of a unique constraint, null is not considered equal.

    UNIQUE KEY can be used only when sql_compatibility is set to 'B', which has the same semantics as UNIQUE.

  • PRIMARY KEY index_parameters

    PRIMARY KEY ( column_name [, ... ] ) index_parameters

    Specifies that a column or columns of a table can contain only unique (non-duplicate) and non-null values.

    Only one primary key can be specified for a table.

  • DEFERRABLE | NOT DEFERRABLE

    They determine whether the constraint is deferrable. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction using the SET CONSTRAINTS command. NOT DEFERRABLE is the default value. Currently, only UNIQUE constraints, primary key constraints, and foreign key constraints accept this clause. All the other constraints are not deferrable.

  • INITIALLY IMMEDIATE | INITIALLY DEFERRED

    If a constraint is deferrable, this clause specifies the default time to check the constraint.

    • If the constraint is INITIALLY IMMEDIATE (default value), it is checked after each statement.
    • If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction.

    The constraint check time can be altered using the SET CONSTRAINTS statement.

  • USING INDEX TABLESPACE tablespace_name

    Allows selection of the tablespace in which the index associated with a UNIQUE or PRIMARY KEY constraint will be created. If not specified, the index is created in default_tablespace. If default_tablespace is empty, the default tablespace of the database is used.

Examples

  • Example 1: Create level-2 partitioned tables of various combination types.
      1
      2
      3
      4
      5
      6
      7
      8
      9
     10
     11
     12
     13
     14
     15
     16
     17
     18
     19
     20
     21
     22
     23
     24
     25
     26
     27
     28
     29
     30
     31
     32
     33
     34
     35
     36
     37
     38
     39
     40
     41
     42
     43
     44
     45
     46
     47
     48
     49
     50
     51
     52
     53
     54
     55
     56
     57
     58
     59
     60
     61
     62
     63
     64
     65
     66
     67
     68
     69
     70
     71
     72
     73
     74
     75
     76
     77
     78
     79
     80
     81
     82
     83
     84
     85
     86
     87
     88
     89
     90
     91
     92
     93
     94
     95
     96
     97
     98
     99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    224
    225
    226
    227
    228
    229
    230
    231
    232
    233
    234
    235
    236
    237
    238
    239
    240
    241
    242
    243
    244
    245
    246
    247
    248
    249
    250
    251
    252
    253
    254
    255
    256
    257
    258
    259
    260
    261
    262
    263
    264
    265
    266
    267
    268
    269
    270
    271
    272
    273
    274
    275
    276
    277
    278
    279
    280
    281
    282
    283
    284
    285
    286
    287
    288
    289
    290
    291
    292
    293
    294
    295
    296
    297
    298
    299
    300
    301
    302
    303
    304
    305
    306
    307
    308
    309
    310
    311
    312
    313
    314
    315
    316
    317
    318
    319
    320
    321
    322
    323
    324
    325
    326
    327
    328
    329
    330
    331
    332
    333
    334
    335
    336
    337
    338
    339
    340
    CREATE TABLE list_list
    (
        month_code VARCHAR2 ( 30 ) NOT NULL ,
        dept_code  VARCHAR2 ( 30 ) NOT NULL ,
        user_no    VARCHAR2 ( 30 ) NOT NULL ,
        sales_amt  int
    )
    PARTITION BY LIST (month_code) SUBPARTITION BY LIST (dept_code)
    (
      PARTITION p_201901 VALUES ( '201902' )
      (
        SUBPARTITION p_201901_a VALUES ( '1' ),
        SUBPARTITION p_201901_b VALUES ( '2' )
      ),
      PARTITION p_201902 VALUES ( '201903' )
      (
        SUBPARTITION p_201902_a VALUES ( '1' ),
        SUBPARTITION p_201902_b VALUES ( '2' )
      )
    );
    insert into list_list values('201902', '1', '1', 1);
    insert into list_list values('201902', '2', '1', 1);
    insert into list_list values('201902', '1', '1', 1);
    insert into list_list values('201903', '2', '1', 1);
    insert into list_list values('201903', '1', '1', 1);
    insert into list_list values('201903', '2', '1', 1);
    select * from list_list;
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 2         | 1       |         1
     201903     | 2         | 1       |         1
     201903     | 1         | 1       |         1
     201902     | 2         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (6 rows)
    
    drop table list_list;
    CREATE TABLE list_hash
    (
        month_code VARCHAR2 ( 30 ) NOT NULL ,
        dept_code  VARCHAR2 ( 30 ) NOT NULL ,
        user_no    VARCHAR2 ( 30 ) NOT NULL ,
        sales_amt  int
    )
    PARTITION BY LIST (month_code) SUBPARTITION BY HASH (dept_code)
    (
      PARTITION p_201901 VALUES ( '201902' )
      (
        SUBPARTITION p_201901_a,
        SUBPARTITION p_201901_b
      ),
      PARTITION p_201902 VALUES ( '201903' )
      (
        SUBPARTITION p_201902_a,
        SUBPARTITION p_201902_b
      )
    );
    insert into list_hash values('201902', '1', '1', 1);
    insert into list_hash values('201902', '2', '1', 1);
    insert into list_hash values('201902', '3', '1', 1);
    insert into list_hash values('201903', '4', '1', 1);
    insert into list_hash values('201903', '5', '1', 1);
    insert into list_hash values('201903', '6', '1', 1);
    select * from list_hash;
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 4         | 1       |         1
     201903     | 5         | 1       |         1
     201903     | 6         | 1       |         1
     201902     | 2         | 1       |         1
     201902     | 3         | 1       |         1
     201902     | 1         | 1       |         1
    (6 rows)
    
    drop table list_hash;
    CREATE TABLE list_range
    (
        month_code VARCHAR2 ( 30 ) NOT NULL ,
        dept_code  VARCHAR2 ( 30 ) NOT NULL ,
        user_no    VARCHAR2 ( 30 ) NOT NULL ,
        sales_amt  int
    )
    PARTITION BY LIST (month_code) SUBPARTITION BY RANGE (dept_code)
    (
      PARTITION p_201901 VALUES ( '201902' )
      (
        SUBPARTITION p_201901_a values less than ('4'),
        SUBPARTITION p_201901_b values less than ('6')
      ),
      PARTITION p_201902 VALUES ( '201903' )
      (
        SUBPARTITION p_201902_a values less than ('3'),
        SUBPARTITION p_201902_b values less than ('6')
      )
    );
    insert into list_range values('201902', '1', '1', 1);
    insert into list_range values('201902', '2', '1', 1);
    insert into list_range values('201902', '3', '1', 1);
    insert into list_range values('201903', '4', '1', 1);
    insert into list_range values('201903', '5', '1', 1);
    insert into list_range values('201903', '6', '1', 1);
    ERROR:  inserted partition key does not map to any table partition
    select * from list_range;
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 4         | 1       |         1
     201903     | 5         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 2         | 1       |         1
     201902     | 3         | 1       |         1
    (5 rows)
    
    drop table list_range;
    CREATE TABLE range_list
    (
        month_code VARCHAR2 ( 30 ) NOT NULL ,
        dept_code  VARCHAR2 ( 30 ) NOT NULL ,
        user_no    VARCHAR2 ( 30 ) NOT NULL ,
        sales_amt  int
    )
    PARTITION BY RANGE (month_code) SUBPARTITION BY LIST (dept_code)
    (
      PARTITION p_201901 VALUES LESS THAN( '201903' )
      (
        SUBPARTITION p_201901_a values ('1'),
        SUBPARTITION p_201901_b values ('2')
      ),
      PARTITION p_201902 VALUES LESS THAN( '201904' )
      (
        SUBPARTITION p_201902_a values ('1'),
        SUBPARTITION p_201902_b values ('2')
      )
    );
    insert into range_list values('201902', '1', '1', 1);
    insert into range_list values('201902', '2', '1', 1);
    insert into range_list values('201902', '1', '1', 1);
    insert into range_list values('201903', '2', '1', 1);
    insert into range_list values('201903', '1', '1', 1);
    insert into range_list values('201903', '2', '1', 1);
    select * from range_list;
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201902     | 2         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201903     | 2         | 1       |         1
     201903     | 2         | 1       |         1
     201903     | 1         | 1       |         1
    (6 rows)
    
    drop table range_list;
    CREATE TABLE range_hash
    (
        month_code VARCHAR2 ( 30 ) NOT NULL ,
        dept_code  VARCHAR2 ( 30 ) NOT NULL ,
        user_no    VARCHAR2 ( 30 ) NOT NULL ,
        sales_amt  int
    )
    PARTITION BY RANGE (month_code) SUBPARTITION BY HASH (dept_code)
    (
      PARTITION p_201901 VALUES LESS THAN( '201903' )
      (
        SUBPARTITION p_201901_a,
        SUBPARTITION p_201901_b
      ),
      PARTITION p_201902 VALUES LESS THAN( '201904' )
      (
        SUBPARTITION p_201902_a,
        SUBPARTITION p_201902_b
      )
    );
    insert into range_hash values('201902', '1', '1', 1);
    insert into range_hash values('201902', '2', '1', 1);
    insert into range_hash values('201902', '1', '1', 1);
    insert into range_hash values('201903', '2', '1', 1);
    insert into range_hash values('201903', '1', '1', 1);
    insert into range_hash values('201903', '2', '1', 1);
    select * from range_hash;
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201902     | 2         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201903     | 2         | 1       |         1
     201903     | 2         | 1       |         1
     201903     | 1         | 1       |         1
    (6 rows)
    
    drop table range_hash;
    CREATE TABLE range_range
    (
        month_code VARCHAR2 ( 30 ) NOT NULL ,
        dept_code  VARCHAR2 ( 30 ) NOT NULL ,
        user_no    VARCHAR2 ( 30 ) NOT NULL ,
        sales_amt  int
    )
    PARTITION BY RANGE (month_code) SUBPARTITION BY RANGE (dept_code)
    (
      PARTITION p_201901 VALUES LESS THAN( '201903' )
      (
        SUBPARTITION p_201901_a VALUES LESS THAN( '2' ),
        SUBPARTITION p_201901_b VALUES LESS THAN( '3' )
      ),
      PARTITION p_201902 VALUES LESS THAN( '201904' )
      (
        SUBPARTITION p_201902_a VALUES LESS THAN( '2' ),
        SUBPARTITION p_201902_b VALUES LESS THAN( '3' )
      )
    );
    insert into range_range values('201902', '1', '1', 1);
    insert into range_range values('201902', '2', '1', 1);
    insert into range_range values('201902', '1', '1', 1);
    insert into range_range values('201903', '2', '1', 1);
    insert into range_range values('201903', '1', '1', 1);
    insert into range_range values('201903', '2', '1', 1);
    select * from range_range;
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 2         | 1       |         1
     201903     | 1         | 1       |         1
     201903     | 2         | 1       |         1
     201903     | 2         | 1       |         1
    (6 rows)
    
    drop table range_range;
    CREATE TABLE hash_list
    (
        month_code VARCHAR2 ( 30 ) NOT NULL ,
        dept_code  VARCHAR2 ( 30 ) NOT NULL ,
        user_no    VARCHAR2 ( 30 ) NOT NULL ,
        sales_amt  int
    )
    PARTITION BY hash (month_code) SUBPARTITION BY LIST (dept_code)
    (
      PARTITION p_201901
      (
        SUBPARTITION p_201901_a VALUES ( '1' ),
        SUBPARTITION p_201901_b VALUES ( '2' )
      ),
      PARTITION p_201902
      (
        SUBPARTITION p_201902_a VALUES ( '1' ),
        SUBPARTITION p_201902_b VALUES ( '2' )
      )
    );
    insert into hash_list values('201901', '1', '1', 1);
    insert into hash_list values('201901', '2', '1', 1);
    insert into hash_list values('201901', '1', '1', 1);
    insert into hash_list values('201903', '2', '1', 1);
    insert into hash_list values('201903', '1', '1', 1);
    insert into hash_list values('201903', '2', '1', 1);
    select * from hash_list;
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 2         | 1       |         1
     201903     | 2         | 1       |         1
     201903     | 1         | 1       |         1
     201901     | 2         | 1       |         1
     201901     | 1         | 1       |         1
     201901     | 1         | 1       |         1
    (6 rows)
    
    drop table hash_list;
    CREATE TABLE hash_hash
    (
        month_code VARCHAR2 ( 30 ) NOT NULL ,
        dept_code  VARCHAR2 ( 30 ) NOT NULL ,
        user_no    VARCHAR2 ( 30 ) NOT NULL ,
        sales_amt  int
    )
    PARTITION BY hash (month_code) SUBPARTITION BY hash (dept_code)
    (
      PARTITION p_201901
      (
        SUBPARTITION p_201901_a,
        SUBPARTITION p_201901_b
      ),
      PARTITION p_201902
      (
        SUBPARTITION p_201902_a,
        SUBPARTITION p_201902_b
      )
    );
    insert into hash_hash values('201901', '1', '1', 1);
    insert into hash_hash values('201901', '2', '1', 1);
    insert into hash_hash values('201901', '1', '1', 1);
    insert into hash_hash values('201903', '2', '1', 1);
    insert into hash_hash values('201903', '1', '1', 1);
    insert into hash_hash values('201903', '2', '1', 1);
    select * from hash_hash;
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 2         | 1       |         1
     201903     | 2         | 1       |         1
     201903     | 1         | 1       |         1
     201901     | 2         | 1       |         1
     201901     | 1         | 1       |         1
     201901     | 1         | 1       |         1
    (6 rows)
    
    drop table hash_hash;
    CREATE TABLE hash_range
    (
        month_code VARCHAR2 ( 30 ) NOT NULL ,
        dept_code  VARCHAR2 ( 30 ) NOT NULL ,
        user_no    VARCHAR2 ( 30 ) NOT NULL ,
        sales_amt  int
    )
    PARTITION BY hash (month_code) SUBPARTITION BY range (dept_code)
    (
      PARTITION p_201901
      (
        SUBPARTITION p_201901_a VALUES LESS THAN ( '2' ),
        SUBPARTITION p_201901_b VALUES LESS THAN ( '3' )
      ),
      PARTITION p_201902
      (
        SUBPARTITION p_201902_a VALUES LESS THAN ( '2' ),
        SUBPARTITION p_201902_b VALUES LESS THAN ( '3' )
      )
    );
    insert into hash_range values('201901', '1', '1', 1);
    insert into hash_range values('201901', '2', '1', 1);
    insert into hash_range values('201901', '1', '1', 1);
    insert into hash_range values('201903', '2', '1', 1);
    insert into hash_range values('201903', '1', '1', 1);
    insert into hash_range values('201903', '2', '1', 1);
    select * from hash_range;
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 1         | 1       |         1
     201903     | 2         | 1       |         1
     201903     | 2         | 1       |         1
     201901     | 1         | 1       |         1
     201901     | 1         | 1       |         1
     201901     | 2         | 1       |         1
    (6 rows)
    
  • Example 2: Specify partitions in a level-2 partitioned table using DML.
    CREATE TABLE range_list
    (
        month_code VARCHAR2 ( 30 ) NOT NULL ,
        dept_code  VARCHAR2 ( 30 ) NOT NULL ,
        user_no    VARCHAR2 ( 30 ) NOT NULL ,
        sales_amt  int
    )
    PARTITION BY RANGE (month_code) SUBPARTITION BY LIST (dept_code)
    (
      PARTITION p_201901 VALUES LESS THAN( '201903' )
      (
        SUBPARTITION p_201901_a values ('1'),
        SUBPARTITION p_201901_b values ('2')
      ),
      PARTITION p_201902 VALUES LESS THAN( '201910' )
      (
        SUBPARTITION p_201902_a values ('1'),
        SUBPARTITION p_201902_b values ('2')
      )
    );
    -- Insert data to a specified level-1 partition.
    insert into range_list partition (p_201901) values('201902', '1', '1', 1);
    -- The actual partition is inconsistent with the specified partition. An error is reported.
    insert into range_list partition (p_201902) values('201902', '1', '1', 1);
    ERROR:  inserted partition key does not map to the table partition
    DETAIL:  N/A.
    -- Insert data to a specified level-2 partition.
    insert into range_list subpartition (p_201901_a) values('201902', '1', '1', 1);
    -- The actual partition is inconsistent with the specified partition. An error is reported.
    insert into range_list subpartition (p_201901_b) values('201902', '1', '1', 1);
    ERROR:  inserted subpartition key does not map to the table subpartition
    DETAIL:  N/A.
    insert into range_list partition for ('201902') values('201902', '1', '1', 1);
    insert into range_list subpartition for ('201902','1') values('201902', '1', '1', 1);
    
    -- Query data in a specified partition.
    select * from range_list partition (p_201901);
     month_code | dept_code | user_no | sales_amt
    ------------+-----------+---------+-----------
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (4 rows)
    
    select * from range_list subpartition (p_201901_a);
     month_code | dept_code | user_no | sales_amt
    ------------+-----------+---------+-----------
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (4 rows)
    
    select * from range_list partition for ('201902');
     month_code | dept_code | user_no | sales_amt
    ------------+-----------+---------+-----------
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (4 rows)
    
    select * from range_list subpartition for ('201902','1');
     month_code | dept_code | user_no | sales_amt
    ------------+-----------+---------+-----------
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (4 rows)
    
    -- Update data in a specified partition.
    update range_list partition (p_201901) set user_no = '2';
    select * from range_list;
    select *from range_list; month_code | dept_code | user_no | sales_amt
    ------------+-----------+---------+-----------
     201902     | 1         | 2       |         1
     201902     | 1         | 2       |         1
     201902     | 1         | 2       |         1
     201902     | 1         | 2       |         1
    (4 rows)
    update range_list subpartition (p_201901_a) set user_no = '3';
    select * from range_list;
     month_code | dept_code | user_no | sales_amt
    ------------+-----------+---------+-----------
     201902     | 1         | 3       |         1
     201902     | 1         | 3       |         1
     201902     | 1         | 3       |         1
     201902     | 1         | 3       |         1
    (4 rows)
    update range_list partition for ('201902') set user_no = '4';
    select * from range_list;
     month_code | dept_code | user_no | sales_amt
    ------------+-----------+---------+-----------
     201902     | 1         | 4       |         1
     201902     | 1         | 4       |         1
     201902     | 1         | 4       |         1
     201902     | 1         | 4       |         1
    (4 rows)
    update range_list subpartition for ('201902','2') set user_no = '5';
    gaussdb=# select *from range_list;
     month_code | dept_code | user_no | sales_amt
    ------------+-----------+---------+-----------
     201902     | 1         | 4       |         1
     201902     | 1         | 4       |         1
     201902     | 1         | 4       |         1
     201902     | 1         | 4       |         1
    (4 rows)
    select * from range_list;
    
    -- Delete data from a specified partition.
    delete from range_list partition (p_201901);
    DELETE 4
    delete from range_list partition for ('201903');
    DELETE 0
    delete from range_list subpartition (p_201901_a);
    DELETE 0
    delete from range_list subpartition for ('201903','2');
    DELETE 0
    -- When sql_compatibility is set to 'B', data can be deleted from multiple partitions.
    delete from range_list as t partition (p_201901_a, p_201901);
    DELETE 0
    
    -- Insert data into a specified partition.
    insert into range_list partition (p_201901)  values('201902', '1', '1', 1)  ON DUPLICATE KEY UPDATE sales_amt = 5;
    insert into range_list subpartition (p_201901_a)  values('201902', '1', '1', 1)  ON DUPLICATE KEY UPDATE sales_amt = 10;
    insert into range_list partition for ('201902')  values('201902', '1', '1', 1)  ON DUPLICATE KEY UPDATE sales_amt = 30;
    insert into range_list subpartition for ('201902','1')  values('201902', '1', '1', 1)  ON DUPLICATE KEY UPDATE sales_amt = 40;
    select * from range_list;
     month_code | dept_code | user_no | sales_amt
    ------------+-----------+---------+-----------
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (4 rows)
    
    -- Merge data into a specified partition.
    CREATE TABLE newrange_list
    (
        month_code VARCHAR2 ( 30 ) NOT NULL ,
        dept_code  VARCHAR2 ( 30 ) NOT NULL ,
        user_no    VARCHAR2 ( 30 ) NOT NULL ,
        sales_amt  int
    )
    PARTITION BY RANGE (month_code) SUBPARTITION BY LIST (dept_code)
    (
      PARTITION p_201901 VALUES LESS THAN( '201903' )
      (
        SUBPARTITION p_201901_a values ('1'),
        SUBPARTITION p_201901_b values ('2')
      ),
      PARTITION p_201902 VALUES LESS THAN( '201910' )
      (
        SUBPARTITION p_201902_a values ('1'),
        SUBPARTITION p_201902_b values ('2')
      )
    );
    insert into newrange_list values('201902', '1', '1', 1);
    insert into newrange_list values('201903', '1', '1', 2);
    
    MERGE INTO range_list partition (p_201901) p
    USING newrange_list partition (p_201901) np
    ON p.month_code= np.month_code
    WHEN MATCHED THEN
      UPDATE SET dept_code = np.dept_code, user_no = np.user_no, sales_amt = np.sales_amt
    WHEN NOT MATCHED THEN  
      INSERT VALUES (np.month_code, np.dept_code, np.user_no, np.sales_amt);
    
    select * from range_list;
     month_code | dept_code | user_no | sales_amt
    ------------+-----------+---------+-----------
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (4 rows)
    
    MERGE INTO range_list partition for ('201901') p
    USING newrange_list partition for ('201901') np
    ON p.month_code= np.month_code
    WHEN MATCHED THEN
      UPDATE SET dept_code = np.dept_code, user_no = np.user_no, sales_amt = np.sales_amt
    WHEN NOT MATCHED THEN  
      INSERT VALUES (np.month_code, np.dept_code, np.user_no, np.sales_amt);
    
    select * from range_list;
     month_code | dept_code | user_no | sales_amt
    ------------+-----------+---------+-----------
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (4 rows)
    
    MERGE INTO range_list subpartition (p_201901_a) p
    USING newrange_list subpartition (p_201901_a) np
    ON p.month_code= np.month_code
    WHEN MATCHED THEN
      UPDATE SET dept_code = np.dept_code, user_no = np.user_no, sales_amt = np.sales_amt
    WHEN NOT MATCHED THEN  
      INSERT VALUES (np.month_code, np.dept_code, np.user_no, np.sales_amt);
    
    select * from range_list;
     month_code | dept_code | user_no | sales_amt
    ------------+-----------+---------+-----------
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (4 rows)
    
    MERGE INTO range_list subpartition for ('201901', '1') p
    USING newrange_list subpartition for ('201901', '1') np
    ON p.month_code= np.month_code
    WHEN MATCHED THEN
      UPDATE SET dept_code = np.dept_code, user_no = np.user_no, sales_amt = np.sales_amt
    WHEN NOT MATCHED THEN  
      INSERT VALUES (np.month_code, np.dept_code, np.user_no, np.sales_amt);
    
    select * from range_list;
     month_code | dept_code | user_no | sales_amt
    ------------+-----------+---------+-----------
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (4 rows)
  • Example 3: Truncate a level-2 partitioned table.
      1
      2
      3
      4
      5
      6
      7
      8
      9
     10
     11
     12
     13
     14
     15
     16
     17
     18
     19
     20
     21
     22
     23
     24
     25
     26
     27
     28
     29
     30
     31
     32
     33
     34
     35
     36
     37
     38
     39
     40
     41
     42
     43
     44
     45
     46
     47
     48
     49
     50
     51
     52
     53
     54
     55
     56
     57
     58
     59
     60
     61
     62
     63
     64
     65
     66
     67
     68
     69
     70
     71
     72
     73
     74
     75
     76
     77
     78
     79
     80
     81
     82
     83
     84
     85
     86
     87
     88
     89
     90
     91
     92
     93
     94
     95
     96
     97
     98
     99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    CREATE TABLE list_list
    (
        month_code VARCHAR2 ( 30 ) NOT NULL ,
        dept_code  VARCHAR2 ( 30 ) NOT NULL ,
        user_no    VARCHAR2 ( 30 ) NOT NULL ,
        sales_amt  int
    )
    PARTITION BY LIST (month_code) SUBPARTITION BY LIST (dept_code)
    (
      PARTITION p_201901 VALUES ( '201902' )
      (
        SUBPARTITION p_201901_a VALUES ( '1' ),
        SUBPARTITION p_201901_b VALUES ( default )
      ),
      PARTITION p_201902 VALUES ( '201903' )
      (
        SUBPARTITION p_201902_a VALUES ( '1' ),
        SUBPARTITION p_201902_b VALUES ( '2' )
      )
    );
    insert into list_list values('201902', '1', '1', 1);
    insert into list_list values('201902', '2', '1', 1);
    insert into list_list values('201902', '1', '1', 1);
    insert into list_list values('201903', '2', '1', 1);
    insert into list_list values('201903', '1', '1', 1);
    insert into list_list values('201903', '2', '1', 1);
    select * from list_list;
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 2         | 1       |         1
     201903     | 2         | 1       |         1
     201903     | 1         | 1       |         1
     201902     | 2         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (6 rows)
    
    select * from list_list partition (p_201901);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201902     | 2         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (3 rows)
    
    alter table list_list truncate partition p_201901;
    select * from list_list partition (p_201901);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
    (0 rows)
    
    select * from list_list partition (p_201902);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 2         | 1       |         1
     201903     | 2         | 1       |         1
     201903     | 1         | 1       |         1
    (3 rows)
    
    alter table list_list truncate partition p_201902;
    select * from list_list partition (p_201902);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
    (0 rows)
    
    select * from list_list;
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
    (0 rows)
    
    insert into list_list values('201902', '1', '1', 1);
    insert into list_list values('201902', '2', '1', 1);
    insert into list_list values('201902', '1', '1', 1);
    insert into list_list values('201903', '2', '1', 1);
    insert into list_list values('201903', '1', '1', 1);
    insert into list_list values('201903', '2', '1', 1);
    select * from list_list subpartition (p_201901_a);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (2 rows)
    
    alter table list_list truncate subpartition p_201901_a;
    select * from list_list subpartition (p_201901_a);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
    (0 rows)
    
    select * from list_list subpartition (p_201901_b);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201902     | 2         | 1       |         1
    (1 row)
    
    alter table list_list truncate subpartition p_201901_b;
    select * from list_list subpartition (p_201901_b);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
    (0 rows)
    
    select * from list_list subpartition (p_201902_a);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 1         | 1       |         1
    (1 row)
    
    alter table list_list truncate subpartition p_201902_a;
    select * from list_list subpartition (p_201902_a);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
    (0 rows)
    
    select * from list_list subpartition (p_201902_b);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 2         | 1       |         1
     201903     | 2         | 1       |         1
    (2 rows)
    
    alter table list_list truncate subpartition p_201902_b;
    select * from list_list subpartition (p_201902_b);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
    (0 rows)
    
    select * from list_list;
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
    (0 rows)
    
    drop table list_list;
    
  • Example 4: Split a level-2 partitioned table.
      1
      2
      3
      4
      5
      6
      7
      8
      9
     10
     11
     12
     13
     14
     15
     16
     17
     18
     19
     20
     21
     22
     23
     24
     25
     26
     27
     28
     29
     30
     31
     32
     33
     34
     35
     36
     37
     38
     39
     40
     41
     42
     43
     44
     45
     46
     47
     48
     49
     50
     51
     52
     53
     54
     55
     56
     57
     58
     59
     60
     61
     62
     63
     64
     65
     66
     67
     68
     69
     70
     71
     72
     73
     74
     75
     76
     77
     78
     79
     80
     81
     82
     83
     84
     85
     86
     87
     88
     89
     90
     91
     92
     93
     94
     95
     96
     97
     98
     99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    CREATE TABLE list_list
    (
        month_code VARCHAR2 ( 30 ) NOT NULL ,
        dept_code  VARCHAR2 ( 30 ) NOT NULL ,
        user_no    VARCHAR2 ( 30 ) NOT NULL ,
        sales_amt  int
    )
    PARTITION BY LIST (month_code) SUBPARTITION BY LIST (dept_code)
    (
      PARTITION p_201901 VALUES ( '201902' )
      (
        SUBPARTITION p_201901_a VALUES ( '1' ),
        SUBPARTITION p_201901_b VALUES ( default )
      ),
      PARTITION p_201902 VALUES ( '201903' )
      (
        SUBPARTITION p_201902_a VALUES ( '1' ),
        SUBPARTITION p_201902_b VALUES ( default )
      )
    );
    insert into list_list values('201902', '1', '1', 1);
    insert into list_list values('201902', '2', '1', 1);
    insert into list_list values('201902', '1', '1', 1);
    insert into list_list values('201903', '2', '1', 1);
    insert into list_list values('201903', '1', '1', 1);
    insert into list_list values('201903', '2', '1', 1);
    select * from list_list;
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 2         | 1       |         1
     201903     | 2         | 1       |         1
     201903     | 1         | 1       |         1
     201902     | 2         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (6 rows)
    
    select * from list_list subpartition (p_201901_a);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (2 rows)
    
    select * from list_list subpartition (p_201901_b);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201902     | 2         | 1       |         1
    (1 row)
    
    alter table list_list split subpartition p_201901_b values (2) into
    (
     subpartition p_201901_b,
     subpartition p_201901_c
    );
    select * from list_list subpartition (p_201901_a);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (2 rows)
    
    select * from list_list subpartition (p_201901_b);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201902     | 2         | 1       |         1
    (1 row)
    
    select * from list_list subpartition (p_201901_c);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
    (0 rows)
    
    select * from list_list partition (p_201901);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201902     | 2         | 1       |         1
     201902     | 1         | 1       |         1
     201902     | 1         | 1       |         1
    (3 rows)
    
    select * from list_list subpartition (p_201902_a);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 1         | 1       |         1
    (1 row)
    
    select * from list_list subpartition (p_201902_b);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 2         | 1       |         1
     201903     | 2         | 1       |         1
    (2 rows)
    
    alter table list_list split subpartition p_201902_b values (3) into
    (
     subpartition p_201902_b,
     subpartition p_201902_c
    );
    select * from list_list subpartition (p_201902_a);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 1         | 1       |         1
    (1 row)
    
    select * from list_list subpartition (p_201902_b);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
    (0 rows)
    
    select * from list_list subpartition (p_201902_c);
     month_code | dept_code | user_no | sales_amt 
    ------------+-----------+---------+-----------
     201903     | 2         | 1       |         1
     201903     | 2         | 1       |         1
    (2 rows)
    
    drop table list_list;