VARIOUS TABLES IN TERADATA

Share via:
VARIOUS TABLES IN TERADATA

Dear Readers.

In this article ,we will see VARIOUS TABLES IN TERADATA

Teradata supports the following table types to hold temporary data.

  • Derived Table
  • Volatile Table
  • Global Temporary Table

Derived Table

Derived tables are created, used and dropped within a query. These are used to store intermediate results within a query.

Example

The following example builds a derived table EmpSal with records of employees with salary greater than 75000.

When the above query is executed, it returns the employees with salary greater than 75000.

Volatile Table

Volatile tables are created, used and dropped within a user session. Their definition is not stored in data dictionary. They hold intermediate data of the query which is frequently used. Following is the syntax.

Syntax

Example

Global Temporary Table

The definition of Global Temporary table is stored in data dictionary and they can be used by many users/sessions. But the data loaded into global temporary table is retained only during the session. You can materialize up to 2000 global temporary tables per session. Following is the syntax.

Syntax

Example

We have two other types of tables in Teradata, SET and Multiset table and the main difference of these table is SET table doesn’t allow duplicate row whereas Multiset table allows it, as below:

A set table does not allow any duplicate row in it.For example

A Multiset table allows duplicate rows in it. For example

There is performance impact involved with SET table. A SET table does not allow duplicate row hence for every new row inserted or updated in the table, Teradata checks on violation of uniqueness constraint for each new row (inserted or updated)this is called duplicate row checking which may overhead on the resources and may cause serious performance issue if number of records are in large number.

To avoid the above performance issue, it is advised that to define UPI, USI or any other uniqueness constraint on any column in SET table.With uniqueness constraint defined on column level, duplicate check won’t happen for entire row rather it will happen on uniqueness constraint hence performance will improve.

Note:- DUPLICATE ROW CHECKS grows exponentially with the number of records per Primary Index(PI) values.

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

 

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment