github.com/cloudberrydb/gpbackup@v1.0.3-0.20240118031043-5410fd45eed6/ci/scale/sql/scaletestdb_wideschema_ddl.sql (about)

     1  DROP SCHEMA IF EXISTS wide CASCADE;
     2  CREATE SCHEMA wide;
     3  
     4  -- wide schema tables, for testing scale of data
     5  CREATE TABLE wide.customer
     6  (C_CUSTKEY INT, 
     7  C_NAME VARCHAR(25),
     8  C_ADDRESS VARCHAR(40),
     9  C_NATIONKEY INTEGER,
    10  C_PHONE CHAR(15),
    11  C_ACCTBAL DECIMAL(15,2),
    12  C_MKTSEGMENT CHAR(10),
    13  C_COMMENT VARCHAR(117))
    14  DISTRIBUTED BY (C_CUSTKEY);
    15  
    16  CREATE TABLE wide.lineitem
    17  (L_ORDERKEY INT,
    18  L_PARTKEY INT,
    19  L_SUPPKEY INT,
    20  L_LINENUMBER INTEGER,
    21  L_QUANTITY DECIMAL(15,2),
    22  L_EXTENDEDPRICE DECIMAL(15,2),
    23  L_DISCOUNT DECIMAL(15,2),
    24  L_TAX DECIMAL(15,2),
    25  L_RETURNFLAG CHAR(1),
    26  L_LINESTATUS CHAR(1),
    27  L_SHIPDATE DATE,
    28  L_COMMITDATE DATE,
    29  L_RECEIPTDATE DATE,
    30  L_SHIPINSTRUCT CHAR(25),
    31  L_SHIPMODE CHAR(10),
    32  L_COMMENT VARCHAR(44))
    33  DISTRIBUTED BY (L_ORDERKEY,L_LINENUMBER)
    34  PARTITION BY RANGE (L_SHIPDATE)
    35  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
    36  default partition others);
    37  
    38  CREATE TABLE wide.lineitem_2
    39  (L_ORDERKEY INT,
    40  L_PARTKEY INT,
    41  L_SUPPKEY INT,
    42  L_LINENUMBER INTEGER,
    43  L_QUANTITY DECIMAL(15,2),
    44  L_EXTENDEDPRICE DECIMAL(15,2),
    45  L_DISCOUNT DECIMAL(15,2),
    46  L_TAX DECIMAL(15,2),
    47  L_RETURNFLAG CHAR(1),
    48  L_LINESTATUS CHAR(1),
    49  L_SHIPDATE DATE,
    50  L_COMMITDATE DATE,
    51  L_RECEIPTDATE DATE,
    52  L_SHIPINSTRUCT CHAR(25),
    53  L_SHIPMODE CHAR(10),
    54  L_COMMENT VARCHAR(44))
    55  DISTRIBUTED BY (L_ORDERKEY,L_LINENUMBER)
    56  PARTITION BY RANGE (L_SHIPDATE)
    57  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
    58  default partition others);
    59  
    60  CREATE TABLE wide.lineitem_3
    61  (L_ORDERKEY INT,
    62  L_PARTKEY INT,
    63  L_SUPPKEY INT,
    64  L_LINENUMBER INTEGER,
    65  L_QUANTITY DECIMAL(15,2),
    66  L_EXTENDEDPRICE DECIMAL(15,2),
    67  L_DISCOUNT DECIMAL(15,2),
    68  L_TAX DECIMAL(15,2),
    69  L_RETURNFLAG CHAR(1),
    70  L_LINESTATUS CHAR(1),
    71  L_SHIPDATE DATE,
    72  L_COMMITDATE DATE,
    73  L_RECEIPTDATE DATE,
    74  L_SHIPINSTRUCT CHAR(25),
    75  L_SHIPMODE CHAR(10),
    76  L_COMMENT VARCHAR(44))
    77  DISTRIBUTED BY (L_ORDERKEY,L_LINENUMBER)
    78  PARTITION BY RANGE (L_SHIPDATE)
    79  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
    80  default partition others);
    81  
    82  CREATE TABLE wide.lineitem_4
    83  (L_ORDERKEY INT,
    84  L_PARTKEY INT,
    85  L_SUPPKEY INT,
    86  L_LINENUMBER INTEGER,
    87  L_QUANTITY DECIMAL(15,2),
    88  L_EXTENDEDPRICE DECIMAL(15,2),
    89  L_DISCOUNT DECIMAL(15,2),
    90  L_TAX DECIMAL(15,2),
    91  L_RETURNFLAG CHAR(1),
    92  L_LINESTATUS CHAR(1),
    93  L_SHIPDATE DATE,
    94  L_COMMITDATE DATE,
    95  L_RECEIPTDATE DATE,
    96  L_SHIPINSTRUCT CHAR(25),
    97  L_SHIPMODE CHAR(10),
    98  L_COMMENT VARCHAR(44))
    99  WITH (appendonly=true)
   100  DISTRIBUTED BY (L_ORDERKEY,L_LINENUMBER)
   101  PARTITION BY RANGE (L_SHIPDATE)
   102  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   103  default partition others);
   104  
   105  CREATE TABLE wide.lineitem_5
   106  (L_ORDERKEY INT,
   107  L_PARTKEY INT,
   108  L_SUPPKEY INT,
   109  L_LINENUMBER INTEGER,
   110  L_QUANTITY DECIMAL(15,2),
   111  L_EXTENDEDPRICE DECIMAL(15,2),
   112  L_DISCOUNT DECIMAL(15,2),
   113  L_TAX DECIMAL(15,2),
   114  L_RETURNFLAG CHAR(1),
   115  L_LINESTATUS CHAR(1),
   116  L_SHIPDATE DATE,
   117  L_COMMITDATE DATE,
   118  L_RECEIPTDATE DATE,
   119  L_SHIPINSTRUCT CHAR(25),
   120  L_SHIPMODE CHAR(10),
   121  L_COMMENT VARCHAR(44))
   122  WITH (appendonly=true)
   123  DISTRIBUTED BY (L_ORDERKEY,L_LINENUMBER)
   124  PARTITION BY RANGE (L_SHIPDATE)
   125  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   126  default partition others);
   127  
   128  CREATE TABLE wide.lineitem_6
   129  (L_ORDERKEY INT,
   130  L_PARTKEY INT,
   131  L_SUPPKEY INT,
   132  L_LINENUMBER INTEGER,
   133  L_QUANTITY DECIMAL(15,2),
   134  L_EXTENDEDPRICE DECIMAL(15,2),
   135  L_DISCOUNT DECIMAL(15,2),
   136  L_TAX DECIMAL(15,2),
   137  L_RETURNFLAG CHAR(1),
   138  L_LINESTATUS CHAR(1),
   139  L_SHIPDATE DATE,
   140  L_COMMITDATE DATE,
   141  L_RECEIPTDATE DATE,
   142  L_SHIPINSTRUCT CHAR(25),
   143  L_SHIPMODE CHAR(10),
   144  L_COMMENT VARCHAR(44))
   145  WITH (appendonly=true)
   146  DISTRIBUTED BY (L_ORDERKEY,L_LINENUMBER)
   147  PARTITION BY RANGE (L_SHIPDATE)
   148  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   149  default partition others);
   150  
   151  CREATE TABLE wide.lineitem_7
   152  (L_ORDERKEY INT,
   153  L_PARTKEY INT,
   154  L_SUPPKEY INT,
   155  L_LINENUMBER INTEGER,
   156  L_QUANTITY DECIMAL(15,2),
   157  L_EXTENDEDPRICE DECIMAL(15,2),
   158  L_DISCOUNT DECIMAL(15,2),
   159  L_TAX DECIMAL(15,2),
   160  L_RETURNFLAG CHAR(1),
   161  L_LINESTATUS CHAR(1),
   162  L_SHIPDATE DATE,
   163  L_COMMITDATE DATE,
   164  L_RECEIPTDATE DATE,
   165  L_SHIPINSTRUCT CHAR(25),
   166  L_SHIPMODE CHAR(10),
   167  L_COMMENT VARCHAR(44))
   168  DISTRIBUTED BY (L_ORDERKEY,L_LINENUMBER)
   169  PARTITION BY RANGE (L_SHIPDATE)
   170  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   171  default partition others);
   172  
   173  CREATE TABLE wide.lineitem_8
   174  (L_ORDERKEY INT,
   175  L_PARTKEY INT,
   176  L_SUPPKEY INT,
   177  L_LINENUMBER INTEGER,
   178  L_QUANTITY DECIMAL(15,2),
   179  L_EXTENDEDPRICE DECIMAL(15,2),
   180  L_DISCOUNT DECIMAL(15,2),
   181  L_TAX DECIMAL(15,2),
   182  L_RETURNFLAG CHAR(1),
   183  L_LINESTATUS CHAR(1),
   184  L_SHIPDATE DATE,
   185  L_COMMITDATE DATE,
   186  L_RECEIPTDATE DATE,
   187  L_SHIPINSTRUCT CHAR(25),
   188  L_SHIPMODE CHAR(10),
   189  L_COMMENT VARCHAR(44))
   190  DISTRIBUTED BY (L_ORDERKEY,L_LINENUMBER)
   191  PARTITION BY RANGE (L_SHIPDATE)
   192  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   193  default partition others);
   194  
   195  CREATE TABLE wide.lineitem_9
   196  (L_ORDERKEY INT,
   197  L_PARTKEY INT,
   198  L_SUPPKEY INT,
   199  L_LINENUMBER INTEGER,
   200  L_QUANTITY DECIMAL(15,2),
   201  L_EXTENDEDPRICE DECIMAL(15,2),
   202  L_DISCOUNT DECIMAL(15,2),
   203  L_TAX DECIMAL(15,2),
   204  L_RETURNFLAG CHAR(1),
   205  L_LINESTATUS CHAR(1),
   206  L_SHIPDATE DATE,
   207  L_COMMITDATE DATE,
   208  L_RECEIPTDATE DATE,
   209  L_SHIPINSTRUCT CHAR(25),
   210  L_SHIPMODE CHAR(10),
   211  L_COMMENT VARCHAR(44))
   212  WITH (appendonly=true)
   213  DISTRIBUTED BY (L_ORDERKEY,L_LINENUMBER)
   214  PARTITION BY RANGE (L_SHIPDATE)
   215  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   216  default partition others);
   217  
   218  CREATE TABLE wide.lineitem_10
   219  (L_ORDERKEY INT,
   220  L_PARTKEY INT,
   221  L_SUPPKEY INT,
   222  L_LINENUMBER INTEGER,
   223  L_QUANTITY DECIMAL(15,2),
   224  L_EXTENDEDPRICE DECIMAL(15,2),
   225  L_DISCOUNT DECIMAL(15,2),
   226  L_TAX DECIMAL(15,2),
   227  L_RETURNFLAG CHAR(1),
   228  L_LINESTATUS CHAR(1),
   229  L_SHIPDATE DATE,
   230  L_COMMITDATE DATE,
   231  L_RECEIPTDATE DATE,
   232  L_SHIPINSTRUCT CHAR(25),
   233  L_SHIPMODE CHAR(10),
   234  L_COMMENT VARCHAR(44))
   235  WITH (appendonly=true)
   236  DISTRIBUTED BY (L_ORDERKEY,L_LINENUMBER)
   237  PARTITION BY RANGE (L_SHIPDATE)
   238  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   239  default partition others);
   240  
   241  CREATE TABLE wide.lineitem_11
   242  (L_ORDERKEY INT,
   243  L_PARTKEY INT,
   244  L_SUPPKEY INT,
   245  L_LINENUMBER INTEGER,
   246  L_QUANTITY DECIMAL(15,2),
   247  L_EXTENDEDPRICE DECIMAL(15,2),
   248  L_DISCOUNT DECIMAL(15,2),
   249  L_TAX DECIMAL(15,2),
   250  L_RETURNFLAG CHAR(1),
   251  L_LINESTATUS CHAR(1),
   252  L_SHIPDATE DATE,
   253  L_COMMITDATE DATE,
   254  L_RECEIPTDATE DATE,
   255  L_SHIPINSTRUCT CHAR(25),
   256  L_SHIPMODE CHAR(10),
   257  L_COMMENT VARCHAR(44))
   258  WITH (appendonly=true)
   259  DISTRIBUTED BY (L_ORDERKEY,L_LINENUMBER)
   260  PARTITION BY RANGE (L_SHIPDATE)
   261  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   262  default partition others);
   263  
   264  CREATE TABLE wide.nation
   265  (N_NATIONKEY INTEGER, 
   266  N_NAME CHAR(25), 
   267  N_REGIONKEY INTEGER, 
   268  N_COMMENT VARCHAR(152))
   269  DISTRIBUTED BY (N_NATIONKEY);
   270  
   271  CREATE TABLE wide.orders
   272  (O_ORDERKEY INT,
   273  O_CUSTKEY INT,
   274  O_ORDERSTATUS CHAR(1),
   275  O_TOTALPRICE DECIMAL(15,2),
   276  O_ORDERDATE DATE,
   277  O_ORDERPRIORITY CHAR(15), 
   278  O_CLERK  CHAR(15), 
   279  O_SHIPPRIORITY INTEGER,
   280  O_COMMENT VARCHAR(79))
   281  DISTRIBUTED BY (O_ORDERKEY)
   282  PARTITION BY RANGE (O_ORDERDATE)
   283  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   284  default partition others);
   285  
   286  CREATE TABLE wide.orders_2
   287  (O_ORDERKEY INT,
   288  O_CUSTKEY INT,
   289  O_ORDERSTATUS CHAR(1),
   290  O_TOTALPRICE DECIMAL(15,2),
   291  O_ORDERDATE DATE,
   292  O_ORDERPRIORITY CHAR(15), 
   293  O_CLERK  CHAR(15), 
   294  O_SHIPPRIORITY INTEGER,
   295  O_COMMENT VARCHAR(79))
   296  DISTRIBUTED BY (O_ORDERKEY)
   297  PARTITION BY RANGE (O_ORDERDATE)
   298  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   299  default partition others);
   300  
   301  CREATE TABLE wide.orders_3
   302  (O_ORDERKEY INT,
   303  O_CUSTKEY INT,
   304  O_ORDERSTATUS CHAR(1),
   305  O_TOTALPRICE DECIMAL(15,2),
   306  O_ORDERDATE DATE,
   307  O_ORDERPRIORITY CHAR(15), 
   308  O_CLERK  CHAR(15), 
   309  O_SHIPPRIORITY INTEGER,
   310  O_COMMENT VARCHAR(79))
   311  WITH (appendonly=true)
   312  DISTRIBUTED BY (O_ORDERKEY)
   313  PARTITION BY RANGE (O_ORDERDATE)
   314  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   315  default partition others);
   316  
   317  CREATE TABLE wide.orders_4
   318  (O_ORDERKEY INT,
   319  O_CUSTKEY INT,
   320  O_ORDERSTATUS CHAR(1),
   321  O_TOTALPRICE DECIMAL(15,2),
   322  O_ORDERDATE DATE,
   323  O_ORDERPRIORITY CHAR(15), 
   324  O_CLERK  CHAR(15), 
   325  O_SHIPPRIORITY INTEGER,
   326  O_COMMENT VARCHAR(79))
   327  DISTRIBUTED BY (O_ORDERKEY)
   328  PARTITION BY RANGE (O_ORDERDATE)
   329  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   330  default partition others);
   331  
   332  CREATE TABLE wide.orders_5
   333  (O_ORDERKEY INT,
   334  O_CUSTKEY INT,
   335  O_ORDERSTATUS CHAR(1),
   336  O_TOTALPRICE DECIMAL(15,2),
   337  O_ORDERDATE DATE,
   338  O_ORDERPRIORITY CHAR(15), 
   339  O_CLERK  CHAR(15), 
   340  O_SHIPPRIORITY INTEGER,
   341  O_COMMENT VARCHAR(79))
   342  DISTRIBUTED BY (O_ORDERKEY)
   343  PARTITION BY RANGE (O_ORDERDATE)
   344  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   345  default partition others);
   346  
   347  CREATE TABLE wide.orders_6
   348  (O_ORDERKEY INT,
   349  O_CUSTKEY INT,
   350  O_ORDERSTATUS CHAR(1),
   351  O_TOTALPRICE DECIMAL(15,2),
   352  O_ORDERDATE DATE,
   353  O_ORDERPRIORITY CHAR(15), 
   354  O_CLERK  CHAR(15), 
   355  O_SHIPPRIORITY INTEGER,
   356  O_COMMENT VARCHAR(79))
   357  WITH (appendonly=true)
   358  DISTRIBUTED BY (O_ORDERKEY)
   359  PARTITION BY RANGE (O_ORDERDATE)
   360  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   361  default partition others);
   362  
   363  CREATE TABLE wide.orders_7
   364  (O_ORDERKEY INT,
   365  O_CUSTKEY INT,
   366  O_ORDERSTATUS CHAR(1),
   367  O_TOTALPRICE DECIMAL(15,2),
   368  O_ORDERDATE DATE,
   369  O_ORDERPRIORITY CHAR(15), 
   370  O_CLERK  CHAR(15), 
   371  O_SHIPPRIORITY INTEGER,
   372  O_COMMENT VARCHAR(79))
   373  DISTRIBUTED BY (O_ORDERKEY)
   374  PARTITION BY RANGE (O_ORDERDATE)
   375  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   376  default partition others);
   377  
   378  CREATE TABLE wide.orders_8
   379  (O_ORDERKEY INT,
   380  O_CUSTKEY INT,
   381  O_ORDERSTATUS CHAR(1),
   382  O_TOTALPRICE DECIMAL(15,2),
   383  O_ORDERDATE DATE,
   384  O_ORDERPRIORITY CHAR(15), 
   385  O_CLERK  CHAR(15), 
   386  O_SHIPPRIORITY INTEGER,
   387  O_COMMENT VARCHAR(79))
   388  WITH (appendonly=true)
   389  DISTRIBUTED BY (O_ORDERKEY)
   390  PARTITION BY RANGE (O_ORDERDATE)
   391  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   392  default partition others);
   393  
   394  CREATE TABLE wide.orders_9
   395  (O_ORDERKEY INT,
   396  O_CUSTKEY INT,
   397  O_ORDERSTATUS CHAR(1),
   398  O_TOTALPRICE DECIMAL(15,2),
   399  O_ORDERDATE DATE,
   400  O_ORDERPRIORITY CHAR(15), 
   401  O_CLERK  CHAR(15), 
   402  O_SHIPPRIORITY INTEGER,
   403  O_COMMENT VARCHAR(79))
   404  DISTRIBUTED BY (O_ORDERKEY)
   405  PARTITION BY RANGE (O_ORDERDATE)
   406  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   407  default partition others);
   408  
   409  CREATE TABLE wide.orders_10
   410  (O_ORDERKEY INT,
   411  O_CUSTKEY INT,
   412  O_ORDERSTATUS CHAR(1),
   413  O_TOTALPRICE DECIMAL(15,2),
   414  O_ORDERDATE DATE,
   415  O_ORDERPRIORITY CHAR(15), 
   416  O_CLERK  CHAR(15), 
   417  O_SHIPPRIORITY INTEGER,
   418  O_COMMENT VARCHAR(79))
   419  DISTRIBUTED BY (O_ORDERKEY)
   420  PARTITION BY RANGE (O_ORDERDATE)
   421  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   422  default partition others);
   423  
   424  CREATE TABLE wide.orders_11
   425  (O_ORDERKEY INT,
   426  O_CUSTKEY INT,
   427  O_ORDERSTATUS CHAR(1),
   428  O_TOTALPRICE DECIMAL(15,2),
   429  O_ORDERDATE DATE,
   430  O_ORDERPRIORITY CHAR(15), 
   431  O_CLERK  CHAR(15), 
   432  O_SHIPPRIORITY INTEGER,
   433  O_COMMENT VARCHAR(79))
   434  WITH (appendonly=true)
   435  DISTRIBUTED BY (O_ORDERKEY)
   436  PARTITION BY RANGE (O_ORDERDATE)
   437  (start('1982-01-01') INCLUSIVE end ('2015-12-31') INCLUSIVE every (5),
   438  default partition others);
   439  
   440  CREATE TABLE wide.part
   441  (P_PARTKEY INT,
   442  P_NAME VARCHAR(55),
   443  P_MFGR CHAR(25),
   444  P_BRAND CHAR(10),
   445  P_TYPE VARCHAR(25),
   446  P_SIZE INTEGER,
   447  P_CONTAINER CHAR(10),
   448  P_RETAILPRICE DECIMAL(15,2),
   449  P_COMMENT VARCHAR(23))
   450  DISTRIBUTED BY (P_PARTKEY);
   451  
   452  CREATE TABLE wide.partsupp
   453  (PS_PARTKEY INT,
   454  PS_SUPPKEY INT,
   455  PS_AVAILQTY INTEGER,
   456  PS_SUPPLYCOST DECIMAL(15,2),
   457  PS_COMMENT VARCHAR(199))
   458  DISTRIBUTED BY (PS_PARTKEY, PS_SUPPKEY);
   459  
   460  CREATE TABLE wide.region
   461  (R_REGIONKEY INTEGER, 
   462  R_NAME CHAR(25),
   463  R_COMMENT VARCHAR(152))
   464  DISTRIBUTED BY (R_REGIONKEY);
   465  
   466  CREATE TABLE wide.supplier 
   467  (S_SUPPKEY INT,
   468  S_NAME CHAR(25),
   469  S_ADDRESS VARCHAR(40),
   470  S_NATIONKEY INTEGER,
   471  S_PHONE CHAR(15),
   472  S_ACCTBAL DECIMAL(15,2),
   473  S_COMMENT VARCHAR(101))
   474  DISTRIBUTED BY (S_SUPPKEY);