SHOW TABLE, SHOW JOIN INDEX, and Column Partitioning - Advanced SQL Engine - Teradata Database

If the output for a SHOW TABLE or SHOW JOIN INDEX request includes a PARTITION BY clause, the partitioning follows. The output is the same for a column-partitioned table or join index except the PARTITION BY clause includes a COLUMN clause. Grouping, if any, is included in the COLUMN clause and not in the column definition list or select expression list. An ADD option is included if the level has column partitioning or if the number of defined partitions for a row partitioning level is less the maximum number of partitions for the level and it is not the first level that has row partitioning.

The following rules apply to the output of a SHOW TABLE or SHOW JOIN INDEX request for a column-partitioned object that has column grouping in a COLUMN clause.

• If all of the column partitions are single-column partitions with system-determined COLUMN or ROW format and without the NO AUTO COMPRESS option, Teradata Database does not return a column grouping following the COLUMN clause.
• If all of the column partitions are single-column partitions except for one that is multicolumn, and all of the column partitions have system-determined COLUMN or ROW format without an NO AUTO COMPRESS option, Teradata Database reports the shorter (in terms of its number of characters) of COLUMN ALL BUT or COLUMN following the COLUMN clause. For example,

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

• If not the case documented by the second bullet in this list, and at least one, but not all, of the column partitions is a single-column partitions with system-determined COLUMN or ROW format without the NO AUTO COMPRESS option, Teradata Database reports COLUMN ALL BUT with any applicable options separated by COMMA characters and ordered by the field ID of the first column of each column partition and, within a column partition, by the field IDs of the columns in the column partition 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 following the COLUMN clause.

  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)

• If none of cases in the first 3 bullets apply, Teradata Database reports COLUMN with any applicable options, separated by COMMA characters and ordered by the field ID of the first column of each column partition and, within a column partition, by the field IDs of the columns in the column partition that specifies all of the column partitions following the COLUMN clause.

  For example,

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