CREATE TABLE and CREATE TABLE AS Usage Notes (Object File System Table Form) - Teradata Vantage

• All the rules that are applicable to define a primary index (PI) on a Block File System (BFS) table are applicable to an OFS PI table. All the restrictions that are applicable to define an OFS NoPI Index table are applicable to an OFS PI table except the following:
  • An OFS PI table can only be a columnar table. If a CREATE TABLE or CREATE TABLE…AS statement attempts to create a non-columnar OFS PI table, an error is returned.
  • When an ORDER BY clause is defined, it must precede PRIMARY INDEX clause. Otherwise, an error is returned. This behavior is consistent with ORDER BY clause syntax evaluation in BFS DDLs.
  • An OFS PI table cannot be created with a sparse map. If a CREATE TABLE or CREATE TABLE…AS statement attempts to create an OFS PI table with sparse map, an error is returned.
• When a OFS PI table is created, a new “hidden” column TD_OFSPIHASH gets added to the column list internally. This column is used to keep track of row hash.You cannot project this column, and it is not visible with SHOW and HELP statements. If a CREATE TABLE or CREATE TABLE…AS statement attempts to create an OFS PI table with a column named TD_OFSPIHASH, an error is returned.
• If a database or user is not defined with default storage or has default storage with override on error, the current behavior of ignoring a PI clause is changed with OFS PI tables. In case of PI tables, except for retaining a primary index clause, the rest of the invalid elements in a CREATE TABLE or CREATE TABLE...AS statement are ignored. A warning is returned in such cases.

Feature Usage Logging