15.00 - Example: Nonpartitioned NoPI Table - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Example: Nonpartitioned NoPI Table

The following SQL text is the table definition for a nonpartitioned NoPI table. Note that this table does not have column partitioning.

     CREATE TABLE orders (
        order_num   INTEGER NOT NULL,
        order_date  DATE NOT NULL,
        item_num    INTEGER COMPRESS NULL,
        item_desc   CHARACTER(30) COMPRESS NULL)
     NO PRIMARY INDEX;

Because NoPI tables are always multiset tables, Teradata Database does not do duplicate row checks. Teradata Database randomly distributes rows or blocks of rows to the AMPs or locally copies them, as is the case for an INSERT … SELECT request, instead of hash distributing them it does for a table with a primary index. In the case of a NoPI table, Teradata Database appends the rows for an AMP to the portion of that table owned by that AMP.

The table header and user data would look something like this.

 

where:

 

Table element …

Specifies …

Partition Number

the internal partition number indicating the column partition and row partitions for this row.

Because there is no partitioning for this example, the partition number for every row in the table is 0.

Bits in the flag byte of a physical row indicate no partitioning so a 2‑byte or 8‑byte partition number of 0 is implied, but does not actually exist in a physical row of a NoPI table.

Hash Bucket

a 20‑bit (or 16‑bit) hash bucket value.

n

the lowest numbered hash bucket for this AMP from the NoPI hash map.

This value increases to the next hash bucket from the NoPI hash map for this AMP if the uniqueness overflows.

Teradata Database does not actually compute n by hashing the values of any columns, but whatever AMP the row is randomly sent initially uses its lowest numbered hash bucket from the NoPI hash map.

Uniqueness

a uniqueness value.

  • The value is 48 bits for a 16‑bit hash bucket.
  • The value is 44 bits for a 20‑bit hash bucket.
  • If the uniqueness value overflows, it resets to 1.

    N

    a nullable column with COMPRESS NULL.

    Presence Bits

    null compression with a presence indicator bit.

    If the presence bit is 1, a value is present; otherwise, no value is present. This means the field is null, and the column takes no space because COMPRESS NULL is specified.

    No presence bits exist if the column is defined as NOT NULL.

    In the example, only 2 presence bits are shown per row because only 2 columns in the table are nullable.