Table attributes can be categorized into these below. See color for reference:
- Table Options – Specifies the physical attributes of the table such as Journal and Fallback.
- Column Definition – Specifies the list of columns, data types and their attributes.
- Index Definition – Additional indexing options such as Primary Index, Secondary Index and Partitioned Primary Index
CREATE [SET/MULTISET] TABLE DATABASENAME.TABLENAME, [NO] FALLBACK, [NO] BEFORE JOURNAL, [NO] AFTER JOURNAL, ( COLUMN1 DATATYPE, COLUMN2 DATATYPE, . . . COLUMNZ DATATYPE ) [UNIQUE] PRIMARY INDEX [INDEXNAME] (COLUMN1,COLUMN2...COLUMNZ) [UNIQUE] INDEX [INDEXNAME] (COLUMN1.COLUMN2...COLUMNG)
[SET/MULTISET]: SET tables discard completely duplicate records however MULTISET allows multiple instance of rows. So it depends on the situation. If you know that you will be getting distinct rows , it is advisable to go with MULTISET table as it will not check for duplicate rows. Hence, saving time and enhancing performance. However if you know that you would be getting duplicate records however you need to process only one of the duplicates rows, Go with SET tables. TERADATA default is SET however ANSI default is MULTISET.
FALLBACK: FALLBACK is TERADATA mechanism to save table data in case of AMP Failure. If you define any table with FALLBACK option , a duplicate copy of table data is maintained in some other AMP. SO in case of AMP Failure, the FALLBACK AMP is used. For critical tables, it is recommended to use FALLBACK option. However it also comes with more Storage Space utilisation disadvantage. In TERADATA, default is NO FALLBACK.
PERMANENT JOURNALS:There are four types of Permanent Journals you can use: BEFORE, AFTER, DUAL BEFORE, DUAL AFTER. The BEFORE JOURNAL holds the image of impacted rows before any changes are made. AFTER JOURNAL holds the image of affected rows after changes are done. In DUAL BEFORE/AFTER Journal, two images are taken and are stored in two different AMP’s. As it was also discussed earlier that PERMANENT JOURNALS use PERMANENT SPACE so if these JOURNALS are not required any more, it is advisable to drop JOURNALS to save some space.