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

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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, Vantage 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, Vantage 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, Vantage 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, Vantage 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))