Teradata supports a variety of properties to be used while table creation which determines how teradata will store data

Teradata Table Properties

1. MULTISET Vs SET (All Teradata tables can be either SET or MULISET)
   • SET tables does not allow two rows in the table to be exactly same.
   • MULTISET tables allow two or more rows in the table to be exactly same.
   • By default, SET table created in TERADATA mode but MULTISET table is created in ANSI mode.
   • It is advisable to define a table as MULTISET and define the UPI for uniqueness of entries in the table in order to avoid an extra check for uniqueness of each and every column entry of a row by the system.
   
   
2. FALLBACK ( Data Protection)
   • A hardware (disk, cpu) or software (vproc) failure causes an AMP to be taken off-line until the problem is corrected. During this period, fallback tables are fully available to users. When the AMP is brought back on-line, the associated vdisk is refreshed to reflect any changes during the off-line period.
   • Fallback protects your data by storing a second copy of each row of a table on an alternate, fallback AMP in the same cluster. If an AMP fails,the system accesses the fallback rows to meet requests. Fallback provides AMP fault tolerance at the table level. With fallback tables, if one AMP fails, all table data is still available. Users may continue to use fallback tables without any loss of available data.
   • During table creation or after a table is created, you may specify whether or not the system should keep a fallback copy. If fallback is specified, it is automatic and transparent.
     Syntax: Adding Fallback to a non Fallback table

     
     ALTER TABLE [databasename].[tablename], FALLBACK;

     
     
   • Fallback guarantees that the two copies of a row will always be on different AMPs. If either AMP fails, the alternate row copy is still available on the other AMP.
   • There is a benefit to protecting your data, but there are costs associated with that benefit. With fallback use, you need twice the disk space for storage and twice the I/O for INSERTs, UPDATEs, and DELETEs. The fallback option does not require any extra I/O for SELECT operations and the fallback I/O will be performed in parallel with the primary I/O.
   • The benefits of fallback include:
     • Protects your data from hardware (disk) failure.
     • Protects your data from software (node) failure.
     • Automatically recovers with minimum recovery time, after repairs or fixes are complete.
     • Non-fallback tables may only be restored from external media in the event of a disaster.
   • Fallback Cluster: A cluster is a group of AMPs that act as a single fallback unit. Clustering has no effect on primary row distribution of the table, but the fallback row copy will always go to another AMP in the same cluster. Loss of two AMPs in the cluster causes the RDBMS to halt.
   
   
3. JOURNAL (Permanent Journal : Data Recovery)
   • Used for recovery from unexpected hardware or software disasters.
   • May be specified for:
     • One or more tables
     • One or more databases
   • Permits capture of before images for database rollback.
   • Permits capture of after images for database rollforward.
   • Permits archiving change images during table maintenance.
   • Reduces need for full-table backups.
   • Provides a means of recovering NO FALLBACK tables.
   • Requires additional disk space for change images.
   • Requires user intervention for archive and recovery activity.
     
     Before Journal: Captures image of the table before the transaction.
     • NO BEFORE JOURNAL
     • DUAL BEFORE JOURNAL

     -> If the JOURNAL keyword is specified without NO or DUAL, then a single copy of the image is maintained unless FALLBACK is in effect or is also specified.
     -> If journaling is requested for a table that uses fallback protection, DUAL images are maintained automatically.

     After Journal: Captures image of the table after the transaction.
     • NO AFTER JOURNAL
     • DUAL AFTER JOURNAL
     • LOCAL AFTER JOURNAL
     • NOT LOCAL AFTER JOURNAL

     -> The NO and DUAL options specify the number of after-change images to be maintained for the table.
     -> NOT LOCAL and LOCAL specify whether single after-image journal rows for non-fallback data tables are written on the same virtual AMP (LOCAL) as the changed data rows, or on another virtual AMP in the cluster (NOT LOCAL).

   Syntax: Change the journalising option of a table.

   
   ALTER TABLE [databasename].[tablename], BEFORE JOURNAL;
   ALTER TABLE [databasename].[tablename], DUAL AFTER JOURNAL;
   

   
   
4. CHECKSUM (Disk I/O Integrity Checking)
   • The Teradata Database permits you to select various levels of disk I/O integrity checking of your table data using one of the following integrity checks ranked in order of their ability to detect errors:
     1. Full end-to-end checksums (ALL) : Detects most bit, byte, and byte string errors.
     2. Statistically sampled partial end-to-end checksums (LOW, MEDIUM, HIGH) : Detects lost writes and intermediate levels of bit, byte, and byte string errors.
        HIGH, MEDIUM, LOW checksum settings are deprecated and are equivalent to specifying ON.
     3. Disable checksum disk I/O integrity checks (NONE) : Detects some forms of lost writes using standard file system metadata verification.
   • The checksum setting applies to primary data rows, fallback data rows, and all secondary index rows for the table.
   • This feature detects and logs disk I/O errors: it does not fix them.
   • The more words used to generate the checksum value, the better able that checksum is to detect disk I/O errors. Because CPU utilization increases as a function of the number of words used to generate a checksum, several different levels of checking are provided so you can adjust disk I/O integrity checking at the level of individual tables as needed, balancing integrity checking against system performance.
   Syntax: Change the integrity checking level

   
   ALTER TABLE [databasename].[tablename], CHECKSUM = HIGH IMMEDIATE; 
   

   
   
5. LOG/NO LOG (The transaction journaling option for a global temporary table)
   • LOG maintains a transaction journal for the temporary table during the time is materialized.
   • NO LOG specifies that no transaction journal is to be kept.
   • You cannot change the LOG/NO LOG properties of a global temporary table if any materialized instances of the table exist anywhere in the Teradata Database.
   
   
6. ON COMMIT DELETE ROWS/PRESERVE ROWS
   • The action to take with the contents of a global temporary table when a transaction ends.
   • DELETE ROWS clears the temporary table of all rows.
   • PRESERVE ROWS retains the rows in the table after the transaction is committed.
   • You cannot change the ON COMMIT properties of a global temporary table if any materialized instance of the table exists anywhere in the Teradata Database.

Syntax/Example 1: Permanent MULTISET table with properties

CREATE MULTISET TABLE [databasename].[tablename],
NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(                                   
  col_1   INTEGER,
  col_2   VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC TITLE 'Column Name 2'
)
PRIMARY INDEX ( col_1 ,col_2 ); 

Syntax/Example 2: Permanent SET table with properties

CREATE SET TABLE [databasename].[tablename],
FALLBACK,
BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(                                   
  col_1   INTEGER,
  col_2   VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC TITLE 'Column Name 2'
)
PRIMARY INDEX ( col_1 ,col_2 ); 

Syntax/Example 3: Temporary MULTISET table with properties

CREATE MULTISET GLOBAL TEMPORARY TABLE [databasename].[tablename],
LOG
(                                   
  col_1   INTEGER,
  col_2   VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC TITLE 'Column Name 2'
)
PRIMARY INDEX ( col_1 ,col_2 )
ON COMMIT PRESERVE ROWS; 

Syntax/Example 4: Temporary SET table with properties

CREATE SET GLOBAL TEMPORARY TABLE [databasename].[tablename],
LOG
(                                   
  col_1   INTEGER,
  col_2   VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC TITLE 'Column Name 2'
)
PRIMARY INDEX ( col_1 ,col_2 )
ON COMMIT DELETE ROWS;