github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/partition_metadata.sql (about)

     1  drop database if exists db1;
     2  create database db1;
     3  use db1;
     4  
     5  drop table if exists lc;
     6  CREATE TABLE lc (
     7                      a INT NULL,
     8                      b INT NULL
     9  )
    10      PARTITION BY LIST COLUMNS(a,b) (
    11  	PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),
    12  	PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),
    13  	PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),
    14  	PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) )
    15  );
    16  
    17  select
    18      table_catalog,
    19      table_schema,
    20      table_name,
    21      partition_name,
    22      partition_ordinal_position,
    23      partition_method,
    24      partition_expression,
    25      partition_description,
    26      table_rows,
    27      avg_row_length,
    28      data_length,
    29      max_data_length,
    30      partition_comment
    31  from information_schema.partitions
    32  where table_name = 'lc' and table_schema = 'db1';
    33  drop table lc;
    34  
    35  drop table if exists client_firms;
    36  CREATE TABLE client_firms (
    37                                id   INT,
    38                                name VARCHAR(35)
    39  )
    40      PARTITION BY LIST (id) (
    41  	PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
    42  	PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
    43  	PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
    44  	PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
    45  );
    46  
    47  select
    48      table_catalog,
    49      table_schema,
    50      table_name,
    51      partition_name,
    52      partition_ordinal_position,
    53      partition_method,
    54      partition_expression,
    55      partition_description,
    56      table_rows,
    57      avg_row_length,
    58      data_length,
    59      max_data_length,
    60      partition_comment
    61  from information_schema.partitions
    62  where table_name = 'client_firms' and table_schema = 'db1';
    63  drop table client_firms;
    64  
    65  drop table if exists tk;
    66  CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col1, col2) PARTITIONS 4;
    67  select
    68      table_catalog,
    69      table_schema,
    70      table_name,
    71      partition_name,
    72      partition_ordinal_position,
    73      partition_method,
    74      partition_expression,
    75      partition_description,
    76      table_rows,
    77      avg_row_length,
    78      data_length,
    79      max_data_length,
    80      partition_comment
    81  from information_schema.partitions
    82  where table_name = 'tk' and table_schema = 'db1';
    83  drop table tk;
    84  
    85  drop table if exists t1;
    86  CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3)) PARTITIONS 6;
    87  select
    88      table_catalog,
    89      table_schema,
    90      table_name,
    91      partition_name,
    92      partition_ordinal_position,
    93      partition_method,
    94      partition_expression,
    95      partition_description,
    96      table_rows,
    97      avg_row_length,
    98      data_length,
    99      max_data_length,
   100      partition_comment
   101  from information_schema.partitions
   102  where table_name = 't1' and table_schema = 'db1';
   103  drop table t1;
   104  
   105  drop table if exists employees;
   106  CREATE TABLE employees (
   107                             emp_no      INT             NOT NULL,
   108                             birth_date  DATE            NOT NULL,
   109                             first_name  VARCHAR(14)     NOT NULL,
   110                             last_name   VARCHAR(16)     NOT NULL,
   111                             gender      varchar(5)      NOT NULL,
   112                             hire_date   DATE            NOT NULL,
   113                             PRIMARY KEY (emp_no)
   114  )
   115      partition by range columns (emp_no)
   116  (
   117      partition p01 values less than (100001),
   118      partition p02 values less than (270001),
   119      partition p03 values less than (450001),
   120      partition p04 values less than (530001),
   121      partition p05 values less than (610001),
   122      partition p06 values less than (MAXVALUE)
   123  );
   124  
   125  select
   126      table_catalog,
   127      table_schema,
   128      table_name,
   129      partition_name,
   130      partition_ordinal_position,
   131      partition_method,
   132      partition_expression,
   133      partition_description,
   134      table_rows,
   135      avg_row_length,
   136      data_length,
   137      max_data_length,
   138      partition_comment
   139  from information_schema.partitions
   140  where table_name = 'employees' and table_schema = 'db1';
   141  drop table employees;
   142  
   143  drop table if exists trp;
   144  CREATE TABLE trp (
   145                       id INT NOT NULL,
   146                       fname VARCHAR(30),
   147                       lname VARCHAR(30),
   148                       hired DATE NOT NULL DEFAULT '1970-01-01',
   149                       separated DATE NOT NULL DEFAULT '9999-12-31',
   150                       job_code INT,
   151                       store_id INT
   152  )
   153      PARTITION BY RANGE ( YEAR(separated) ) (
   154  	PARTITION p0 VALUES LESS THAN (1991),
   155  	PARTITION p1 VALUES LESS THAN (1996),
   156  	PARTITION p2 VALUES LESS THAN (2001),
   157  	PARTITION p3 VALUES LESS THAN MAXVALUE
   158  );
   159  
   160  select
   161      table_catalog,
   162      table_schema,
   163      table_name,
   164      partition_name,
   165      partition_ordinal_position,
   166      partition_method,
   167      partition_expression,
   168      partition_description,
   169      table_rows,
   170      avg_row_length,
   171      data_length,
   172      max_data_length,
   173      partition_comment
   174  from information_schema.partitions
   175  where table_name = 'trp' and table_schema = 'db1';
   176  drop table trp;