Need to analyze before creating a table in Teradata

Primary Index

The primary index (PI) distributes the records in a table across the AMPs, by hashing the columns that make up the PI to determine which records go to which AMP. If no PI is specified when a table is created, the first column of the table will be used as the PI.

When creating a table, care needs to be taken to choose a column or set of columns that evenly distribute the data across the AMPs. A PI that distributes data unevenly will at the very least impact the performance of the table, and depending on the size of the table, has the potential to negatively impact the entire system.

Even distribution of the PI isn’t the only criteria to use when choosing a PI. Consideration should also be given to how the data will be queried. If the data can be evenly distributed using different sets of columns, then the determination of which columns to use should be based on how the data will be queried and what other tables it will be joined to. If two tables that are frequently joined have the same PI, then joining them doesn’t require the records to be redistributed to other AMPs to satisfy a query.

A PI doesn’t have to be the same as the primary key (PK) of a table. The purpose of a PI is to evenly distribute the data, while the purpose of a PK is to identify unique records. The PI and PK can be the same, but it isn’t required.

Skew Factor

A table that has perfectly distributed data has a skew factor of 0%. The higher the skew factor is, the more unevenly data in a table is distributed. As a general rule, tables with a skew factor higher than 50% should be evaluated to determine if a different primary index would distribute the data better, and thereby improve performance.

Tables with fewer records than the number of AMPs will have a higher skew factor that 0%, simply because the records cannot be evenly distributed across all of the AMPs. For tables that have fewer records than the number of AMPs the skew factor of the table may not be improved by choosing a different primary index.

Table Creation

Along with choosing the PI of a table, another choice needs to be made when creating a table. The two options are SET and MULTISET. A SET table prohibits duplicate records with identical values in every column from existing in the table, while a MULTISET table allows them.

When using FastLoad or the TPT Load Operator, if you attempt to insert duplicate records into a SET table, the duplicates are discarded without any notification that an attempt to insert duplicates took place. 

A SET table with no unique primary index has a performance overhead because every record that is inserted must be evaluated to determine if a duplicate already exists in the table. This overhead can be minimized by defining a unique index on the table (see the Teradata Database Database Design manual for more information on minimizing duplicate row checks for tables without unique primary indexes).

Create table syntax examples:

CREATE SET TABLE … (results in a SET table being created)

CREATE MULTISET TABLE … (results in a MULTISET table being created)

CREATE TABLE … (results in a SET table being created with Teradata semantics mode and results in a MULTISET table being created with ANSI semantics mode)

Imp Concepts

Create New Table from Existing Table.

CREATE TABLE <databasename>.<tablename> AS
(Select   <columnnames>
FROM    <tablename>
WHERE condition)
with data;

CREATE TABLE <databasename>.<tablename> AS
(Select   <columnnames>
FROM    <tablename>
WHERE condition)
with no data;

What is Perm space , Spool space and Temp space.

Perm space=> It is a maximum amount of disk space for storing user data rows in any table located in the database. However, if no tables are stored within a database,  it is not required to have PERM space. Although a database without PERM space cannot store tables, it can store views and macros because they are physically stored in the Data Dictionary (DD) PERM space and require no user storage space. The DD is in a “database” called DBC.

Spool Space=> SPOOL space is workspace used for the temporary storage of rows during the execution of user SQL statements. unused PERM space is automatically available for use as SPOOL.

Temp Space=> TEMP space is allocated to any databases/users where Global Temporary Tables are used. Unused perm space is available for TEMP space.

some useful query

Use of Extract Function.
SELECT DATE;

 

Date      
 ----------
 2011-09-22 

 

SELECT EXTRACT (YEAR FROM date) as "year";

year   
 -------
 2011    

 

SELECT EXTRACT (MONTH FROM date) as "month";

month   
 --------
 9       

To get the current system date.

select DATE;

To get the current system time and current session ‘Time Zone’ displacement.
select CURRENT_TIME;

To get all the objects in a database.
help database databasename;

It returns the current system timestamp (including year, month and day) and current session Time Zone displacement.
select CURRENT_TIMESTAMP;

 To get the amount of space occupy by each table in there respective Database.

select databasename,
       tablename,
       CAST ( sum(currentperm)/1024/1024/1024 AS DECIMAL(38,2) ) AS "SIZE(GB)"
from dbc.tablesize
where databasename = 'DATABASENAME'
group by databasename , tablename;

 To find all the Database and the amount of space used by it.

SELECT DatabaseName,
       SUM(MaxPerm)/(1024*1024*1024) (DECIMAL(15,6)) as "Max Perm (GB)",
       SUM(CurrentPerm)/(1024*1024*1024) (DECIMAL(15,6)) as "Current Perm (GB)",
       ((SUM(CurrentPerm))/ NULLIFZERO (SUM(MaxPerm)) * 100) (DECIMAL(15,6)) as "Percent Used"
FROM DBC.DiskSpace
WHERE MAXPERM >0
GROUP BY 1
ORDER BY 4 DESC;

Introduction of Teradata Database

The concept of teradata system is little bit different. There is only one database(your teradata system) with lots of (schemas/users). Here (schemas/users) is called database.
In this system dbc is the owner of all metadata. In dbc you can find all the object of teradata.

To get all tables name which consist the particular column.

select tablename, columnname from dbc.columns where columnname ='Emp_Id' and databasename = 'EMPDB';

To see all the objects in a particular database.

Help database databasename;