Example: SHOW TABLE for a Column-Partitioned Table or Join Index - Advanced SQL Engine - Teradata Database

The following rules apply to the report returned by a SHOW TABLE request for a column-partitioned table or a SHOW JOIN INDEX request made on a column-partitioned join index.

For a partitioned table or join index, Vantage includes an ADD option for a partitioning level if the level has column partitioning or if the number of defined partitions for a row partitioning level is less than the maximum number of partitions for the level and the level is not the first level that has row partitioning. The output is the same as for a row-partitioned table or join index if the object only has row partitioning and an ADD option was not specified for the level and the number of row partitions currently defined is the same as the maximum number of row partitions for the level.

The rules for the ADD option are as follows.

• ADD 0 is not reported for any row partitioning level.
• ADD is not reported for the first row partitioning level.
• ADD is reported for a row partitioning level if the number of defined partitions is less than the maximum number of partitions and it is not the first row partitioning level.
• ADD is reported for a column partitioning level, even if it specifies ADD 0.

If ADD is reported, the value is equal to the maximum number of partitions minus the currently defined number of partitions for that partitioning level.

For a column-partitioned table or join index, the output is the same except the PARTITION BY clause includes a COLUMN clause. Grouping is included in the COLUMN clause, not in the column definition list for a table or the select list for a join index. The column grouping in the COLUMN clause is reported as follows.

If all the column partitions are single-column partitions with system-determined COLUMN or ROW format and without a NO AUTO COMPRESS specification, Vantage does not report a column grouping following the COLUMN clause.

If all the column partitions are single-column partitions except for 1 that is multicolumn, and all column partitions have system-determined COLUMN or ROW format without a NO AUTO COMPRESS specification, Vantage reports the shorter in terms of characters of these bullet items following the COLUMN clause.

• COLUMN ALL BUT ((multicolumn_partition_column_list_separated_by_ 
 commas_and_ordered_by_field_ID ))

  or

• COLUMN (columns_in_single-column_partitions_separated_by_commas_and_ordered_by_field ID )

For example,

     COLUMN ALL BUT ((d, p, z))
     COLUMN (a, b, c, g)

If the partitioning is not that of the previous bullet, and at least one, but not all, of the column partitions are single column partitions with system-determined COLUMN or ROW format without the NO AUTO COMPRESS option that specifies all the column partitions except for the single-column partitions with system-determined COLUMN or ROW format without the NO AUTO COMPRESS option is included following the COLUMN clause.

Vantage reports the different forms with entries separated by commas and ordered by field ID.

• COLUMN ALL BUT (single_columns_list_with_options )

  or

• COLUMN ALL BUT (multiple_columns_list_with_options )

  or

• COLUMN ALL BUT (single_columns_list_with_options , (multiple_columns_list_with_options))

For example,

     COLUMN ALL BUT (ROW d, (i, t), k NO AUTO COMPRESS, 
                     COLUMN(m, s, u, v))
     COLUMN ALL BUT (COLUMN(j, m, o))
     COLUMN ALL BUT (ROW(e, j, z) NO AUTO COMPRESS)

Otherwise, if none of the preceding cases, Vantage reports one of the following forms with entries separated by commas and ordered by the field ID of the first column of each column partition. Within a column partition, Vantage orders the entries by the field IDs of the partition columns.

• COLUMN (single_columns_list_with_options )

  or

• COLUMN (multiple_columns_list_with_options )

  or

• COLUMN (single_columns_list_with_options, (multiple_columns_list_with_options ))

For example,

     COLUMN (ROW d, (i, t), k NO AUTO COMPRESS, COLUMN(m, s, u, v))

This example creates column-partitioned table t1 with the following definition.

     CREATE TABLE t1 (
       a INTEGER, 
       b INTEGER, 
       c INTEGER, 
       d INTEGER, 
       e INTEGER, 
       f INTEGER, 
       g INTEGER, 
       h INTEGER,
       i INTEGER, 
       j INTEGER, 
       k INTEGER, 
       l INTEGER, 
       m INTEGER, 
       n INTEGER, 
       o INTEGER, 
       p INTEGER, 
       q INTEGER, 
       r INTEGER,
       s INTEGER,
       t INTEGER, 
       u INTEGER, 
       v INTEGER, 
       w INTEGER, 
       x INTEGER, 
       y INTEGER, 
       z INTEGER)
     NO PRIMARY INDEX 
     PARTITION BY COLUMN ALL BUT (a, b, (g, d), ROW(s, t, j), 
                                  h NO AUTO COMPRESS, x) ADD 65509;
 

Assume that the value of AutoCompressDefault has changed to 2. A SHOW TABLE request for t1 returns the following information.

     SHOW TABLE t1;
      *** Text of DDL statement returned. 
      *** Total elapsed time was 1 second.
-----------------------------------------------------------------------
CREATE MULTISET TABLE  user_name.t1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
a INTEGER,
b INTEGER,
c INTEGER,
d INTEGER,
e INTEGER,
f INTEGER,
g INTEGER,
h INTEGER,
i INTEGER,
j INTEGER,
k INTEGER,
l INTEGER,
m INTEGER,
n INTEGER,
o INTEGER,
p INTEGER,
q INTEGER,
r INTEGER,
s INTEGER,
t INTEGER,
u INTEGER,
v INTEGER,
w INTEGER,
x INTEGER,
y INTEGER,
z INTEGER)
NO PRIMARY INDEX
PARTITION BY COLUMN AUTO COMPRESS
  ALL BUT ((d, g), h NO AUTO COMPRESS, ROW(j, s, t)) ADD 65509;

Columns a, b, and x are not included in the grouping clause for the COLUMN clause because they are single-column partitions by default with system-determined format and autocompression. The column partitions and columns for a column partition are in the same order as in the definition list.