Teradata Columnar

A container can contain one or more column partition values. A subrow contains only one column partition value. A column partition value can consist of one or more column values. Containers and subrows are both physical rows for the file system (one or more physical rows occur in a data block). A "row" of a table (consisting of values for all the columns of the table) is limited to 65KB. For a column-partitiond (CP) table, a table row is broken up into one more column partition values (one for each column partition). For a subrow to be bigger than 65KB, the table row that it is part of would have to be bigger than 65KB but that would exceed the limit for a table row.

A subrow is the same as a regular row (in regard to physcial rows in which they are stored) except that subrow contains values for a subset of one or more columns of the table while a regular row has values for all the columns of the table.

CREATE TABLE t1 (COLULMN(a1 INT, a2 INT), ROW(a3 int, a4 CHAR(60000)) PARTITION BY COLUMN;

This has 2 column partitions: one with a1 and a2 that is stored in containers and another with a3 and a4 that is stored in subrows.

Insert t1 (1, 2, 3, 'big string');

The values 1 and 2 are appended into a container for the first column partition as one column partition value (other column partition values can be in the container -- another container is started when it gets full) while the value 3 and 'big string' are appended as a subrow of the second partition (only one column partition value can go in this subrow -- the next column partition value for this column partition would go into a separate subrow).

***Table Data***
a1 a2 a3 a4
1 2 3 big string <-- up to 65KB
4 5 6 big string2 <-- up to 65KB
7 8 9 big string3 <-- up to 65KB

So the data would be arranged into the container and subrows as shown below right?

CONTAINER#1: 1,2,4,5,7,8,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_, _,_,_....up to 65KB
CONTAINER#2: _,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_,_, _,_,_....up to 65KB

SUBROW#1: 3, big string <-- subrow can't be bigger than 65KB; if a1, a2, a3, a4 can't be bigger than 65KB then
this will be less than 65KB since it only has values for a3 and a4.
SUBROW#2: 6, big string
SUBROW#3: 9, big string
SUBROW#4: _, _

That means to exceed the 65K limit of subrow, first off the table row limit of 65KB should be exceeded? Yes.

For each of the column partition values of a row, the rowid for each of those column partition values is different but the only portion of the rowid that is different is the column partition number (they have the same row partition numbers, hash bucket, and uniqueness value). The column partition values are stored in the file system in rowid order (that is, in order by the internal partition number, and then for the same internal partition number, in order by the hash bucket value, and within the same internal partition number and hash bucket pair by the uniquness value).

By the name, one might think that a uniqueness value is unique for a table but it is really only unique within an internal partition number/hash bucket pair (or for a PI table, internal partition number/hash value pair). It is the combination of internal partition/hash bucket/uniqueness (that is, a rowid) that is unique for the column partition values for a table.

Note that the uniqueness value starts at one and is incremented by one for each internal partition / hash bucket pair for which a column partition value is inserted. If the table is not also row partitioned, this is the same as starting at one and incrementing by one for each row inserted. But with row partitioning, it starts at one and incremented by one for each row inserted into a combined row partition. So the uniqueness value is not unique across all rows of the table -- it is only unique within an internal partition number/hash bucket pair.

For more information on rowids for a NoPI table and a CP table, see the Orange Book on Columnar.

Autocompression is most effective for single-column partitions with COLUMN format and less so for multicolumn partitions with COLUMN format because of the compression techniques used. Effective here means effective in providing a reduction in disk space usage.

For run length compression and value-list compression, compression is better if column partition values are very nonunique. Adding more column values into a column partition value tends to make the column partition values more unique and makes the compression less effective.

The trimming compression techniques only trim the column partition value so adding more column values into a column partition value usually means only the last column value in the column partition value is being trimmed. If the column values were in separate column partitions, trimming might be applicable to each of the column values.

Similiar issues occur for the other techniques if there are multiple columns in a column partition.

Note that with ROW format there are currently no techniques being used for autocompression. So to a have chance that autocompression is effective, currently the column partition must have COLUMN format.

Autocompression and other forms of compression have some CPU overhead so a consideration can be whether the compression ratio achieved, reduction in disk space usage, and the reduced I/O due to compression is worth the extra CPU overhead to compress and uncompress the data. On an I/O bound system or disk space limited system, compression may be worth it but if CPU bound, it may not.

The simple answer to my knowledge is no. A user-defined table function could possibly be used to do this. Also, this might be done with some join logic though that might be complex and not perform well. If readers have other suggestions, please let us know.

Aside: Note that an insert does take a row and properly insert the data into a column-partitioned table but this isn't changing a row to be column.

A table is just a series of physical rows, each with a rowid, where the physical rows are kept in in rowid order. The first portion of the rowid is the partition number (or combined partition number for multilevel partitioning) so rows with the same partition number would be grouped together (with no gaps between if there are empty partitions between them). If there are no rows for a particular partition, it does not take any space (and just isn't there). A partition is not "created" until a physical row is inserted into it (there is really no actual creation process for a partition other than to insert a row for it). For the CREATE TABLE statement, you are just defining the partitioning expression which is later to used to compute the partition number (and part of determining the rowid) for a row when it is inserted. So there are not any performance issues related to "creating" partitions. Similarily, if all the rows for partition are deleted, there is no special action -- you just delete the rows for it and the partition just no longer has any rows.

When creating a table, a table header row is written to each AMP and the data dictionary is updated. But no other space is actually used until rows start being inserted.

This is unlike some other database systems where a partition has to have a data space allocated for it prior to insertion of rows into the partition.

Mani, I am working on a response to a your comments (delayed because I just came back from a sailing around some exotic islands in French Polynesia).

In the meantime, here is a quick response to Cvinodh's comment:

A partition can end in the middle of data block and then another partition can start in the data block. The physical rows are ordered by their internal partition number in the Teradata file system -- a data block is just one or more physical rows (and a data block is the unit of what can be written or read at a time). For a large table, there will be, on an AMP, data blocks for containers of only C1, then possibly a data block with some containers for C1 and then some containers for C2, then data blocks for only containers of C2, then possibly a data block with some containers for C2 and then some containers of C3, and finally data blocks for only containers of C3. That is, partition boundaries are not necessarily on data block boundaries but all the C1 containers comes first then the containers for C2 and then the containers for C3. So usually a data block only has containers for one column partition but, at the boundaries, it will change from one partition to the next partition. So for your question, the "former" is true except for this minor caveat and the "latter" is false.

All the column values of a logical row do not usually exist in same data block -- the column values of a logical row are split across column partitions. For a large table, the column partitions would be large and span many data blocks. It would be only in very small table that the column values of a logical row could exist in the same data block (that is, when all the column partitions fit in the same data block). However, do note that all the column values for a logical row would be on the same AMP.

Note that this is not a "hybrid" columnar approach where the columns are only partitioned within a "data block" or "compression unit" and all the column values of a logical row can be found within that data block or compression unit. Teradata supports a fully columnar approach where each column is separate from the others so to read the data for a particular column only entails reading the data blocks for that column (where possibly the first and the last data block read may contain some values for a preceding column or a following column, respectively). Teradata also supports multicolumn partitions.

For more information, refer to the Orange Book: Teradata Columnar and the Orange Book: Increased Partition Limit and other Partitioning enhancements.

Mani, below I try to answer your questions:

1. As I understand, though it is Column Partition, the data is stored as Row only.

>> All data in Teradata is stored in what I refer to as physical rows by the Teradata file system. A physical row does not always correspond to what is considered to be a row in a table (or to what I refer to as a table row or logical row). The data in a physical row can be a table header, a portion of table header, a regular row (which does correspond to a table row), an index row, container (a series of column partition values; that is, a column partition with COLUMN format), a subrow (one column partition value; that is, a column partition with ROW format), and other miscellaneous data structures. A physical row always has a row header which includes the length of the physical row and a rowid. The Teradata file system keeps physical rows in order by rowid (of which the internal partition number is the first component and therefore, phsyical rows with the same internal partition number are grouped together). Information in the tableid and in the table header is used to determine what kind of data is in a physical row.

2. But it contains the Row or Column values in the Subrow or Containers.

>> If a column partition has ROW format, the physical rows for that column partition will have subrow format (that is, a one column partition value). If a column partition has COLUMN format, the physical rows for that column partition will have container format (that is, one or more column partition values – usually a lot of column partition values). For a single-column partition, a column partition value is one value of the column. For a multicolumn partition, a column partition value is the concatenation of the values of those columns from the same table row.

3. Can you please explain how the Containers and Subrows are distributed in the System?

>> Since a CP table is a NoPI table, single-row and array inserts are randomly distributed to the AMPs. On the AMP, the table row is then split into column partition values for each of the column partitions and, if the column partition has COLUMN format, a column partition value is appended to last existing container if there is one and there is room, or a new container is appended with that column partition value. If the column partition has ROW format, the column partition value for the column partition is appended to the column partition as a subrow. The random distribution should usually provide a fairly even distribution of the rows to the AMPs.

>> For an INSERT-SELECT, rows from the source are not distributed by default but rather are locally copied on each AMP from the source (the result of the SELECT) to the CP table and then each table row is split up as described above. If the source is distributed fairly equally, the data for the CP table should be fairly evenly distributed among the AMPs; but if not, the CP table would have the same skew as the source. However, there is an option to force the INSERT-SELECT to randomly distribute the rows from the source before inserting them into a NoPI or CP table and an option to specify columns on which to hash distribute rows – these options (using the HASH BY clause) should be used in the case the source is or might be skewed to make sure table rows are distributed equally among the AMPs.

4. Is it distributed among AMPs equally?

>> This depends on how the table rows are inserted into the CP table as described above. Also, a copy/restore to a different configuration or a reconfig can cause a NoPI or CP table to become skewed.

5. Will this cause any Hot AMP Conditions?

>> This can cause hot AMP conditions for SELECTs from the table if an INSERT-SELECT is used to load the table from a skewed source without using the HASH BY clause. However, as mentioned above the HASH BY option should be used when the source is skewed in order to avoid an unequal distribution of the rows among the AMPs.

6. Is a CP Table a good candidate for Huge Volume of Data?

>> Having a large volume of data is an indication that CP should be considered but there are also a number of other things to consider in regard to whether a table is actually a good candidate to be CP. In particular, the kinds of queries that will occur is an important factor to consider. There is also a cost to loading data into a CP table (that is, to partition the rows of the table into the column partitions) which needs to be considered. There is also usually a trade-off in that I/O may decrease but CPU usage may increase. For other considerations and more information about Teradata Columnar, see the Orange Book: Teradata Columnar.

Cvinodh asked:

If the logical row is now distributed across different datablocks or cylinders in a column partitioned NOPI table... Then conventional Master Index and Cylinder Index (with starting RowID and highest Row hash value) entries are not applicable for these tables right? so these tables dont have any of these structures?

>> Actually, they do have these structures. These structures are used by the Teradata file system to maintain physical rows in rowid order and to find physical rows based on a rowid (for instance, rowids coming from an index or rowid spool). These structures have the starting rowid of a cylinder or data block and the internal partition number (this is zero for a nonpartitioned table) and the first 32 bits of the rest of the highest rowid for the cylinder or data block. In a primary index table, these 32 bits would correspond to a row hash; however, for a NoPI table (and a CP table is a NoPI table), part of these 32 bits are a hash bucket value chosen from the NoPI hash map and part of them are the high order bits of the uniqueness (a NoPI table has more uniqueness bits in the rowid than a PI table). The Teradata file system really doesn't care what these bits represent -- it just orders the physical rows by the rowid it is given for each of the physical rows. For more information about the rowid structure for a NoPI and a CP table, see the Orange Book: No Primary Index (NoPI) Table User's Guide and the Orange Book: Teradata Columnar.