Exadata to Teradata

A SET Table with a non-unique index will degrade performance.

1.      SET table does not allow any duplicates even if the index is unique or non-unique.

2.      SET tables put an overhead on the system since Teradata needs to check duplicates every time a new row is inserted or updated.

3.      If an UPI is defined, teradata checks uniqueness only on these columns. It won't check the complete row to check the uniqueness. So overhead is reduced. Any USI or any column with a UNIQUE or PRIMARY KEY constraint can also be used.

4.      By default, a SET table is created.

5.      In case of an INSERT INTO SELECT * FROM <TABLE> statement, duplicate rows will be filtered automatically for SET tables, which means that no error occurs.

Go for a MULTISET table when…

1.      If you are sure that there will be no duplicates form source, choose MULTISET as it will reduce the overhead on the system in checking for duplicates.

Primary Index in Teradata can be non-unique. It is not same as Oracle's Primary Key.

1.      unique index distributes the data evenly across all the amps. This makes the data retrieval faster in parallel.

2.      Sometimes a non-unique index will be preferred when non-unique columns are used in the joins.

Next, I will be learning the below...

1. Which is better? A SET table with UPI or a MULTISET with UPI? Will a multiset table with an unique index have duplicates? Will such table be created? If so, would it have been created to improve the performance as the data is evenly distributed across the amps.
2. There is no way to change a SET table into a MULTISET table after it has been created. IS the opposite possible after removing the dupes?
3. Why Teradata is not best suitable for an OLTP system?
4. Why oracle is using share everything even when it reduces DW performance? One of the reasons for superior DW performance in Teradata is due to share nothing architecture.
5. What types of partitions Teradata have? Column partition and …
6. What is the use of Secondary Index?
7. What is fast load, TPT, BTEQ?
8. How can we load data from a flat file into Teradata and Oracle?