Teradata: Primary Index

Primary Index

Every table must have at least one column as the Primary Index. The Primary Index is defined when the table is created.There are two reasons you might pick a different Primary Index then your Primary Key. They are (1) for Performance reasons and (2) known access paths.

Primary Index Rules
Rule 1: One Primary Index per table.
Rule 2: A Primary Index value can be unique or non-unique.
Rule 3: The Primary Index value can be NULL.
Rule 4: The Primary Index value can be modified.
Rule 5: The Primary Index of a populated table cannot be modified.
Rule 6: A Primary Index has a limit of 64 columns.

Two Types of Primary Indexes (UPI or NUPI)

Unique Primary Index(UPI)
A Unique Primary Index (UPI) is unique and cannot have any duplicates.
If you try and insert a row with a Primary Index value that is already in the table, the row will be rejected. An UPI enforces UNIQUENESS for a column.
 
A Unique Primary Index (UPI) will always spread the rows of the table evenly amongst the AMPs. UPI access is always aone-AMP operation.
 
We have selected EMP_NO to be our Primary Index. Because we have designated EMP_NO to be a Unique Primary Index, there can be no duplicate employee numbers in the table.       

Non-Unique Primary Index (NUPI) 
A Non-Unique Primary Index (NUPI) means that the values for the selected column can be non-unique. Duplicate values can exist.

A Non-Unique Primary Index will almost never spread the table rows evenly.
An All-AMP operation will take longer if the data is unevenly distributed. You might pick a NUPI over an UPI because the NUPI column may be more effective for query access and joins.

We have selected LAST_NAME to be our Primary Index. Because we have designated LAST_NAME to be a Non-Unique Primary Index we are anticipating that there will be individuals in the table with the same last name.

Multi-Column Primary Indexes:
Teradata allows more than one column to be designated as the Primary Index. It is still only one Primary Index, but it is merely made up by combining multiple columns together. Teradata allows up to 64 combined columns to make up the one Primary Index required for a table.

On the following page you can see we have designated First_Name and Last_Name combined to make up the Primary Index.
 This is often done for two reasons:
 (1) To get better data distribution among the AMPs
 (2) Users often use multiple keys consistently to query

Data distribution using Primary Index 
When a user submits an SQL request against a table using a Primary Index, the request becomes a one-AMP operation, which is the most direct and efficient way for the system to find a row. The process is explained below.

Hashing Process
1.The primary index value goes into the hashing algorithm.
2.The output of the hashing algorithm is the row hash value.
3.The hash map points to the specific AMP where the row resides.
4.The PE sends the request directly to the identified AMP.
5.The AMP locates the row(s) on its vdisk.
6.The data is sent over the BYNET to the PE, and the PE sends the answer set on to the client application.

NOTE:
If you forget to define PRIMARY INDEX while doing a CREATE TABLE, the default will be use i.e. on the following:

* PRIMARY key

* First UNIQUE constraint

* First column

PRIMARY KEY	PRIMARY INDEX
It cannot be NULL	NULL is possible
It is not mandatory in Teradata	It is mandatory in Teradata
No data distribution	Helps in data distribution
It has to be unique	Unique or non-unique
It is logical implementation	Physical implementation
Cannot be changed	can be changed

Teradata: Teradata Multiset vs. Set Tables

SET VS MULTISET

Table Type Specifications of SET VS MULTISET

There are two different table type philosophies so there are two different type tables. They are SET and MULTISET. It has been said, “A man with one watch knows the time, but a man with two watches is never sure”. When Teradata was originally designed it did not allow duplicate rows in a table. If any row in the same table had the same values in every column Teradata would throw one of the rows out. They believed a second row was a mistake. Why would someone need two watches and why would someone need two rows exactly the same? This is SET theory and a SET table kicks out duplicate rows. The ANSI standard believed in a different philosophy. If two rows are entered into a table that are exact duplicates then this is acceptable. If a person wants to wear two watches then they probably have a good reason. This is a MULTISET table and duplicate rows are allowed. If you do not specify SET or MULTISET, one is used as a default. Here is the issue: the default in Teradata mode is SET and the default inANSI mode is MULTISET.

Therefore, to eliminate confusion it is important to explicitly define which one is desired. Otherwise, you must know in which mode the CREATE TABLE will execute in so that the correct type is used for each table. The implication of using a SET or MULTISET table is discussed further.

SET and MULTISET Tables

A SET table does not allow duplicate rows so teradata checks to ensure that no two rows in a table are exactly the same. This can be a burden. One way around the duplicate row check is to have a column in the table defined as UNIQUE. This could be a Unique Primary Index (UPI), Unique Secondary Index (USI) or even a column with a UNIQUE or PRIMARY KEY constraint. Since all must be unique, a duplicate row may never exist. Therefore, the check on either the index or constraint eliminates the need for the row to be examined for uniqueness. As a result, inserting new rows can be much faster by eliminating the duplicate row check.

However, if the table is defined with a NUPI and the table uses SET as the table type, now a duplicate row check must be performed. Since SET tables do not allow duplicate rows a check must be performed every time a NUPI DUP (duplicate of an existing row NUPI value) value is inserted or updated in the table. Do not be fooled! A duplicate row check can be a very expensive operation in terms of processing time. This is because every new row inserted must be checked to see if it is a duplicate of any existing row with the same NUPI Row Hash value. The number of checks increases exponentially as each new row is added to the table.

What is the solution? There are two: either make the table a MULTISET table (only if you want duplicate rows to be possible) or define at least one column or composite columns as UNIQUE. If neither is an option then the SET table with no unique columns will work, but inserts and updates will take more time because of the mandatory duplicate row check.

If you define UPI on MULTISET is absolutely valid however it destroys the actual usage of MULTISET tables.

SET tables do not allow duplicate rows to be inserted, and it is not allowed to create a duplicate row with an UPDATE statement. MULTISET tables do not have these restrictions.

Apart from differences mentioned above, you may have to deal with several performance issues when working with SET tables.

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

If you run an INSERT INTO
VALUES (a, b) statement, you will receive an error message in case you try to insert duplicate rows.

There is no way to change a SET table into a MULTISET table after it has been created. I must admit that have not found out yet why this limitation exists.

There is a performance impact involved with SET tables. Each time a row is inserted or updated, Teradata has to check if the next row to be added would violate the uniqueness constraint. This is called DUPLICATE ROW CHECK, and will seriously degrade performance if many rows with the same primary index are inserted. The number of checks increases exponentially with each new row added to the table!

There is no performance impact for SET tables when there is a UPI (Unique primary index) defined on the table. As the UPI itself ensures uniqueness, no DUPLICATE ROW CHECK will be performed.

Instead of an UPI you can as well use any USI (Unique Secondary Index), or any column with a UNIQUE or PRIMARY KEY constraint.

Basically, anything which ensures uniqueness and therefore allows Teradata to bypass the DUPLICATE ROW CHECK, is welcome in such a situation.

SET tables are good candidates for performance improvement. The easiest way to find all of them is to look at the relevant DBC.TABLES entries:

SELECT * FROM DBC.TABLES WHERE checkopt = ‘N’ AND TABLEKIND = ‘T’;  — Set Tables

SELECT * FROM DBC.TABLES WHERE checkopt = ‘Y’ AND TABLEKIND = ‘T’;  — Multiset tables

Whenever the uniqueness of entries is guaranteed programmatically – such is the case in  a GROUP BY statement – you can redesign your table easily from SET to MULTISET.

You can achieve some performance gain, depending on how many records per PI value you have.

Keep in mind that the number of DUPLICATE ROW CHECKS grows exponentially with the number of records per Primary Index (PI).

Here is an example to demonstrate the impact of having a SET table, and many duplicate primary index values:

We create two identical tables, which will be the target tables for our example:

CREATE SET  TABLE TMP_SET
(
PK INTEGER NOT NULL,
DESCR INTEGER NOT NULL
) PRIMARY INDEX (PK);

CREATE MULTISET  TABLE TMP_MULTISET
(
PK INTEGER NOT NULL,
DESCR INTEGER NOT NULL
) PRIMARY INDEX (PK);

— In a next step we figure out our session id, as we will need it to analyze the resource usage:

SELECT SESSION;

7376827

— We insert random data into the Set and Multiset table, but only use 500 different Primary Index values to cause
— some impact on performance. The “descr” column has to be quite unique as row level duplicates would be filtered
INSERT INTO TMP_MULTISET
SELECT
RANDOM(1,500) AS x,
RANDOM(1,999999999) AS descr
FROM
;
;

INSERT INTO TMP_SET
SELECT
RANDOM(1,500) AS x,
RANDOM(1,999999999) AS descr
FROM
;
;

— We check the CPU and DISK IO usage for the SET and the MULTISET table:

SELECT * FROM DBC.DBQLOGTBL WHERE SESSIONID = 7376827;

	Total IO	CPU Seconds
Set	126.827,00	20,15
Multiset	3.556,00	0,96

Above example shows that much more disk accesses and CPU seconds are used in the case of a SET table.

Actually, there IS a difference between SET and MULTISET even if you have a UNIQUE index:

CREATE TABLE T1 (c1 INTEGER NOT NULL, c2 INTEGER) PRIMARY INDEX (c1);

INSERT T1 VALUES (1,1);

INSERT T1 VALUES (1,2);

INSERT T1 VALUES (2,1);

CREATE SET TABLE T2_SET (c1 INTEGER NOT NULL) UNIQUE PRIMARY INDEX (c1);

CREATE MULTISET TABLE T2_MULTISET (c1 INTEGER NOT NULL) UNIQUE PRIMARY INDEX (c1);

INSERT T2_SET SELECT c1 FROM T1;/* succeeds - quietly eliminates the duplicate and inserts 2 rows */

INSERT T2_MULTISET SELECT c1 FROM T1;/* fails with duplicate key error */

Reference:

http://www.dwhpro.com/teradata-multiset-tables/

http://lakshmikishore.blogspot.com/2010/12/table-type-specifications-of-set-vs.html

Teradata

By default Teradata database treats first column as Primary Index

What is a B-TEQ in Teradata?.         

It is client s/w that resides on network or channel-attached host.

After starting BTEQ,you can log on to Teradata using a TDPid (Teradata Director Program id) with your user id and password.  The TDPid identifies the instance of TD you are going to access.

Use of BTEQ (Basic Teradata Query program) is to submit SQL queries to Teradata Database. It works like a interface b/w query and TD.

What is Tera Data Explain Command ?.

The Teradata EXPLAIN command can be added to the front of the SQL statement and while execution it gives a detailed analysis for particular statement.

The EXPLAIN command can be used to estimate what are the indexes to be used,how much time a query will take and how many rows a query will be return.

 This command can be used as a useful debug tool to resolve problems with long processing times.

1) COMPRESS:

COMPRESS is not a complex data reducing algorithm. It does not reduce repeating characters (or bits), or character (or bit) patterns within rows, columns, blocks or cylinders.

COMPRESS practically eliminates the data storage of nulls (or 256 constant values) for fixed-length, non-primary index columns.

2) Rules for compression:

·  Column must be fixed-length, 255 characters or less, and not part of the primary index.

.  The following types of data can be compressed:Nulls, Zeros, Blanks, Any numeric data type,DATE (expressed as COMPRESS (DATE 'yyyy-mm-dd')),

   CHARACTER(up to 255 characters).

·  The default setting is no compression.

The syntax for the compression attribute is as follows:

            CREATE SET TABLE tbl1,

            ( col1    CHAR(1)  COMPRESS,    /* Compresses nulls */

              col2    CHAR(1)  COMPRESS 'product' )  /* Compresses nulls plus the  

              value 'product' */

            PRIMARY INDEX ...;

The compress column values are case specific. The value "PRODUCT" does not get compressed, if "product" is the specified compression value.

The below are the advantages of data compression in Teradata.

·      Compression results in shorter rows which are generally more performant.

·      Column compression provides the following capacity and performance benefits:

              Reduced capacity

            Reduced I/O traffic

            Moderate CPU savings

·    The I/O savings correlates to the percentage of data compressed out of a row.

·    Table backups use the compressed format, so backups will also be faster.

Disadvantage is It does not compress varchar columns.

Multiload is a Teradata utility that will read a unix file and update (insert, delete, update) a populated target table.

Multiload is faster than Bteq for updating a populated table. Bteq updates 1 row at a time, where Multiload updates blocks of rows at a time.

When Multiload is compared to the Fastload/delete/insert method, then Multiload is faster for volumes above 10,000 records. For volumes less than 10,000 records, the difference is seconds, and is negligible. Multiload is faster whether the target table has a unique primary index, or a non-unique primary index.

The Fastload

Teradata Fastload is a utility which loads records into an empty table using blocks of records and multiple amps. Fastload is composed of 2 phases, the first phase reads the unix records, and writes them to TOS buffers on the amps. The second phase reads the TOS buffers and writes them to a TOS database table.

Fastload/delete/insert method of updating populated target tables consists of 3 steps:

 1) Fastload into a temporary table

 2) Bteq delete matching rows from the target table

 3) Bteq insert into the target table.

Multiload skips the temporary table and directly updates the target table from the unix file.

When loading into an empty target table, both Multiload and Fastload are about the same speed.

Multiload’s speed is not affected by the number of rows already in the target table. The speed is affected by the number of update records, and can be affected by the number of error records written to the error journals.

The following are Teradata Database Versions .

1. Teradata v2r5

2. Teradata v2r6

3. Teradata 12

4. Teradata 13

5. Teradata 13.10

6. Teradata 14

Reference: http://teradata-sql.blogspot.com/2012/04/compression-in-teradata.html