Continue reading »]]> Before you start with SI, well I must say that as a prerequisite you must first read about the Primary index in Teradata.


So after knowing about Primary index the point here is that when we already have UPI and NUPI then what’s the use of this Secondary Index?

Well the best possible answer for this question is that – Secondary Indexes provide an alternate path to the data, and should be used on queries that run many times.

Teradata runs extremely well without secondary indexes, but since secondary indexes use up space and overhead, they should only be used on “KNOWN QUERIES” or queries that are run over and over again. Once you know the data warehouse, environment you can create secondary indexes to enhance its performance.

Syntax of creating Secondary Index

Syntax of UNIQUE SI:

CREATE UNIQUE INDEX (Column/Columns) ON <dbname>.<tablename >;

Syntax of NON-UNIQUE SI:

CREATE INDEX (Column/Columns) ON <dbname>.<tablename >;

Note – SI can be created even after table is populated with data. Unlike PI which is created only at the time of creation of table. You can create and drop SI at any time.

Whenever you create SI on the table, Teradata will create a subtable on all AMP. This subtable contains three columns given below –

1. Secondary Index Value
2. Secondary Index Row ID (this is the hashed value of SI value)
3. Base table Row ID (this is the actual base row id  )

Will see the use of all these values later in this post.

USI Subtable Example

When we defined a UNIQUE SI on the table, then Teradata will immediately create a USI subtable in each AMP for that particular table.


Remember creation of subtable requires PERM space, so always be wise to choose your SI. Normally the best SI is that column or columns which is mostly used in the WHERE clause.

Now I’ll explain in-depth architecture of creation of subtable and retrieval of SI for better understanding of the concept.

Please look into the image below -

Suppose we have an Employee table (base table) having attributes Emp, Dept Fname, Lname and Soc_security. We defined USI on the column Soc_Security.

You can see the SI subtable created on each AMP which holds information about the SI column and corresponding Base row id (Base Table Row-ID), which is the ROW ID of the actual Employee table. The steps involve to load this subtable is as follows -

1)      Teradata will first create the subtable on all AMP.

2)      After that it hashes the value of this USI column (Soc_Security) and based on that hashed value it check the hash map for the AMP number which will hold this USI value in its subtable.

3)      After getting the respective AMP number, the SI value along with the two more attributes (secondary index row id and base table row id) will be stored in the subtable of that AMP.

In this way we populate our USI subtable on each AMP. As the SI columns is UNIQUE there is no duplication of SI values in any subtable, means each row in the subtable is unique and will fetch only one row when we make a query on that SI column.

Note - As it is clear now that defining SI will require the creation of subtable, so we should be aware that SI requires space cost factor on our Teradata system.

Teradata retrieval of USI query.

Suppose on the above example we make a query –

Select * from Employee_table where Soc_Security = ’123-99-8888′;

When a TD optimizer finds USI in where clause it knows that it’s a 2 AMP operation and also only one row will be returned. So the step its perform for retrieval is as follows –

1)      It will hash the value of SI (’123-99-8888′), by hashing algorithm and found the hash value for it.

2)      Now it checks this hash value in the hash map and gets the AMP number from it. We know that this AMP stores this SI value.

3)      Now it will go to the Employee subtable of that AMP and retrieve the Base row id which is stored for that hash value.

4)      This Base row id will be sent back to optimizer by BYNET.

5)      Now optimizer sent back this ROW ID again and fetch the resultant row from the Base table for which Soc_Security = ’123-99-8888′ .

As we have seen that Teradata system requires 2 AMP to reach the answer row that’s why we called USI operation as the 2 AMP operations. Even if SI row resides in the same AMP  in which Base row reside , still after getting Base row id from the subtable it will sent back to optimizer so that it start search again based on that Base row id. So it’s always called as the 2 amp operation.

NUSI Subtable Example 

When we defined a NUSI on the table then Teradata will build the subtable on each AMP in the same fashion as that in USI. The only difference in this subtable creation is that, instead of building subtable on each AMP it will be build on AMP local which means that each AMP will build the subtable in it to points it own base rows. In other words each NUSI subtable will reflect and points to the those base rows only which it owns.

Please look into the image below -

Suppose we have an Employee table (base table) on which we defined NUSI on the column Fname.

1)      Now Teradata will first create the subtable on all AMP.

2)      Each AMP will hold the secondary index values for their rows in the base table only. In our example, each AMP holds the Fname column for all employee rows in the base table on their AMP (AMP local).

3)      Each AMP Local Fname will have the Base Table Row-ID (pointer) so the AMP can retrieve it quickly if needed. If an AMP contains duplicate first names, only one subtable row for that name is built with multiple Base Row-IDs. See the example above for the Fname = ‘John’ , the subtable holds multiple base row id for this value.

Teradata retrieval of USI query.

Suppose on the above example we make a query –

Select * from Employee_table where Fname = ‘John’;

When an NUSI (Fname) is used in the WHERE clause of an SQL statement, the PE Optimizer recognizes the Non-Unique Secondary Index. It will perform an all AMP operation to look into the subtable for the requested value. So the step its perform for retrieval is as follows –

1)      It will hash the value of NUSI (‘John’), by hashing algorithm and found the hash value for it.

2)      Now it will instruct all AMP to look for this hash value in its Employee subtable. Note unlike USI there is no looking into hash map  because each subtable in the AMP contains rows from its own base rows only. So this look up on hash value will be performed on all AMP subtable.

3)       Any AMP which doesn’t have this hash value will not participate anymore in the operation.

4)      When the hash value found the corresponding Base row id will be fetched from the subtable and send to optimizer for actual retrieval of rows.

The point to note here is that NUSI operation is not similar to FTS (full table scan).

Suppose we don’t have Fname as the NUSI and we make the query on Fname in WHERE clause. In this case first of all Fname from the Employee table is redistributed in SPOOL space and then we match our value given in the where clause from the rows in SPOOL.

While in our case where Fname is defined as NUSI, TD optimizer already knows that this column is NUSI and its already distributed by its value in subtable in each AMP. So it will not go for redistribution step instead of that it will directly match the value for it in each subtables.

The PE will decide if a NUSI is strongly selective and worth using over a Full Table Scan. So it’s advisable to always do COLLECT STATS on NUSI index. You can check the Explain function to see if a NUSI is being utilized or if bitmapping (FTS) is taking place.

 Secondary Index Summary


1)      You can have up to 32 secondary indexes for a table.

2)      Secondary Indexes provide an alternate path to the data.

3)      The two types of secondary indexes are USI and NUSI.

4)      Every secondary index defined causes each AMP to create a subtable.

5)      USI subtables are hash distributed.

6)      NUSI subtables are AMP local.

7)      USI queries are Two-AMP operations.

8)      NUSI queries are All-AMP operations, but not Full Table Scans.

9)      Always Collect Statistics on all NUSI indexes.

Any suggestions, corrections or enhancements to the post are most welcome

Continue reading »]]> When you want to retrieve the rows from the table, the very first step in explain plan is the pseudo table lock on that table


e.g -

Type select * from <databasename>.<tablename> in the SQL assistant, and do the explain plan for this table. The very first step you see is -

1) First, we lock a distinct <databasename>.”pseudo table” for read on a
RowHash to prevent global deadlock for
<databasename>.<tablename>.

2)Next, we lock <databasename>.<tablename> for read.

We know that for retrieval of rows from the table we need to put the read lock which we are implementing in step 2, but the question is that what is this pseudo table lock in the step 1 ?

We know that each AMP holds a portion of a table. We also know that when a Full Table Scan is performed that each AMP will read their portion of the table.

Now suppose that two different users wants to place multiple locks on the same table and one user gets one lock and the other user gets another lock. Both user requires lock made by other user and have to wait for indefinite time to acquire that lock because actually both the users are waiting for each other to release lock. This is called DEADLOCK.


A Pseudo Lock is how Teradata prevents a deadlock.

When a user does an All-AMP operation Teradata will assign a single AMP to command the other AMPs to lock the table. We can call this AMP as the “Gatekeeper” AMP. This AMP will always be responsible for locking that particular table on all AMPs. Now all the users running an all AMP query on the table have to report to this “Gatekeeper” AMP for getting permission on locks.

The “Gatekeeper” AMP never plays favorites and performs the locking on a First Come First Serve basis. The first user to run the query will get the lock. The others will have to wait. In this way Teradata prevents the deadlock situation when an all AMP operation is made in the query


Note – Teradata selects this “Gatekeeper” AMP by hashing the tablename used in the select query and then matching the hash value in the hash map. The AMP number which it gets from hash map is assigned as “Gatekeeper” AMP.

Refer the image below taken from Coffings to understand the concept better -

Continue reading »]]>

We know that in Teradata all the tables are either SET or MULTISET.

The difference between SET and MULTISET tables is –


 SET tables – SET tables did not allow duplicate values in the table.

e.g.

COLUMN 1          COLUMN 2          COLUMN 3          COLUMN 4

A                            b                            c                            d

H                           g                            y                            k

A                            b                            c                            d             (Not allowed)

Syntax

CREATE SET TABLE <table_name> ………

………

MULTISET tables – MULTISET tables allow duplicate values in table.

e.g.

COLUMN 1          COLUMN 2          COLUMN 3          COLUMN 4

A                            b                            c                            d

H                           g                            y                            k

A                            b                            c                            d             (allowed)

Syntax

CREATE MULTISET TABLE <table_name>………

………

If not specified in the DDL of the table then Teradata will create table as default SET. A SET table force Teradata to check for the duplicate rows every time a new row is inserted or updated in the table. This is an overhead on the resource if we need to insert massive amount of rows.

Which table to choose?

Before creating the table it’s very important to know what kind of data is required in the table and based on that we must define SET or MULTISET.


Remember that SET table causes an additional overhead of checking for the duplicate records. So we need to follow few points to save Teradata from this additional overhead.

• If you are using any GROUP BY or QUALIFY statement on the source table then it’s highly recommended to define target table as MULTISET. As GROUP BY and QUALIFY will remove the duplicate records from the source.
• If the source table has UPI (Unique Primary Index) then also there is no need of SET target table. As UPI will never allows duplicate PI in the same table.

So with the help of little bit of awareness about SET and MULTISET we can save a lot of time while loading the table.


Key Points to remember.

• If we are inserting data using INSERT into SEL from clause then SET table check for duplicate rows will removed automatically and there will be no DUPLICATE ROW ERROR.
• If we are inserting data using INSERT into VALUES clause then SET table check for duplicate rows will not be removed automatically and there will be DUPLICATE ROW ERROR.

Continue reading »]]>

Teradata database has become so popular today that you will often find many Teradata certification courses and online Teradata certification training resources.

To become an Teradata Certified Professional, you will need to learn all the Teradata certification test objectives. Most of us are concern about the practice or sample questions for various Teradata exams. These sample questions are beneficial in testing your knowledge and boost your confidence to appear for the real Exam.

I have collected few sample questions for the second exam of Teradata 12 certification i.e. TD SQL Exam (TEO-122)

There are 15 questions, all of them focused on the SQL Exam only.

So those interested in certification have a look on the questions and rate yourself before appearing in TD certification.

Click below link to start -

TD 12 SQL Exam (TEO-122) – Sample 1

You may also want to check below mentioned Sample Papers for completer reference -

TD 12 Basic Exam (TEO-121) – Sample 1

TD 12 Basic Exam (TEO-121) – Sample 2

I have also identified few E books which is immensely useful for Teradata Certification Preparation. Please have a look and if you are seriously preparing for TD certifications then don’t hesitate to purchase it.(Hover the mouse on the below image to get more information about the book)

Disclaimer – These questions are collected from various sources on internet and teradatatech is not claiming for their occurrence in the real teradata certification exams. Please treat these questions as the practice set and test your knowledge accordingly.

Continue reading »]]>

Teradata is consistent with hashing a value. It means that when Teradata hashes the Index value (e.g. ‘teradatatech’) it will come up with a Row Hash. Now if Teradata hashes the same index value (‘teradatatech’) million times it will always come up with the same Row Hash. Teradata always hashes the same value with consistent results.

However, Teradata can hash two very different values and the result can sometimes be the same Row Hash. This is a called a Collision. It is sometimes called a Synonym.


The AMP always double checks the Primary Index Value to insure it is getting the correct row. The AMP double checks because it is afraid if it does not it could get the wrong row because of a Collision!

Teradata searches for a Primary Index value by hashing the Primary Index Value and then using the Row Hash and the Hash Map to point to a specific AMP. The AMP then uses using a Binary Search of the Row Hash to find the row it is looking for.


For e.g. we have the name ‘teradatatech’ and ‘teradata’ and that they both have the same Row Hash of 11000. This is a collision.


This is why the AMP double checks. It finds ‘teradatatech’ with a binary search on 11000, but checks the value for integrity.

↓↓↓↓↓↓↓↓ SHARE IT WITH YOUR FRIENDS  ↓↓↓↓↓↓↓↓

Continue reading »]]> Statistics are data demographics (or hints) used by the Teradata optimizer.


There are many ways to generate a query plan for a given SQL, and collecting statistics ensures that the optimizer will have the most accurate information to create the best access and join plans.

The optimizing phase of Teradata, makes decisions on how to access table data. These decisions can be very important when table joins (especially those involving multiple joins) are required by a query. By default, the Optimizer uses approximations of the number of rows in each table (known as the cardinality of the table) and of the number of unique values in indexes in making its decisions. To build such estimates, the Optimizer picks a random AMP and builds the information and it is possible for the estimates to be significantly off. This can lead to poor choices of join plans, and associated increases in the response times of the queries involved.

One way to help the Optimizer make better decisions is to give it more accurate information as to the content of the table. This can be done using the COLLECT STATISTICS statement. When the Optimizer finds that there are statistics available for a referenced table, it will use those statistics instead of using estimated table cardinality or estimated unique index value counts.

Stats should be collected mainly under the below circumstances:


1. A thumb rule is to collect statistics when they’ve changed by 10%. (That would be 10% more rows inserted, or 10% of the rows deleted, or 10% of the rows changed, or some combination.)

2. The range of values for an index or column of a table for which statistics have been collected has changed significantly. Sometimes one can infer this from the date and time the statistics were last collected, or by the very nature of the column (for instance, if the column in question holds a transaction date, and statistics on that column were last gathered a year ago, it is almost certain that the statistics for that column are stale).

How stats are built over the table?


TD builds the uniqueness count for each identified column / set of columns for the completed table/partition data and stores the information in the DBC tables.

Whenever the stats are collected later, the previously collected information is lost and fresh stats are updated in the DBC tables.

The time taken to collect stats doesn’t depend on how frequently the stats have been collected or how recently the stats have been collected.

Stats should be collected on all dimensions, history, transactional, reference and aggregate tables based on the below approach:


1. If the table is loaded under DELETE INSERT mode, then STATS should be collected during each load.

2. If the table is built under INSERT UPDATE mode, then STATS should be collected if the data demographics change by more than 10%.

3. If the target is a transactional table loaded in APPEND mode, then STATS should be collected if the data demographics change by more than 10%.

4. If the table is built under INSERT mode; (aggregate tables where data is built for a particular duration and queried upon this duration) tables where partitions are built over each aggregation period, STATS should be collected on the new partition, even if the data demographics for the entire table changes less than 10%, because user queries or extractions might be built over data for current period of aggregation.

↓↓↓↓↓↓↓↓ SHARE IT WITH YOUR FRIENDS  ↓↓↓↓↓↓↓↓

Continue reading »]]>

The join index JOIN the two tables together and keeps the result set in the permanent space of Teradata. This JOIN index will hold the result set of the two table, and at the time of JOIN parsing engine will decide whether it is fast to build the result set from the actual BASE tables or the JOIN index. User never directly query the JOIN index. In the sense JOIN index is the result of joining two tables together so that parsing engine always decide to take the result set from this JOIN index instead of going and doing manual join on the base table.

Types of JOIN index -
Multi table JOIN index

Suppose we have two BASE tables EMPLOYEE_TABLE and DEP_TABLE, which holds the data of EMPLOYEE and DEPARTMENT respectively. Now a JOIN index on these two tables will be somewhat-

This way the JOIN index EMP_DEPT holds the result set of two BASE tables, and at the time of JOIN PE will decide weather it is faster to join actual tables or to take result set from this JOIN index. So always choose wise list of columns and tables to create JOIN index.

Single Table JOIN index

A single table JOIN index duplicate a single table, but changes the primary index. Users will only query the base table and its PE who decide which result set is faster, from JOIN index or from actual BASE tables. The reason to create the single table JOIN index is so joins can be performed faster because no redistribution or duplication needs to occur.

Aggregate JOIN index

An aggregate JOIN index will allow the tracking of Averages SUM and COUNT on any table. This JOIN index is basically used if we need to perform any aggregate function in the data of the table.

The main fundamentals of JOIN indexes are -

• JOIN index is not a pointer to data it actually store data in PERM space
• Users never query them directly, its PE who decide which result set to take
• Updated when base tables are changed
• Can’t be loaded with Fastload or Multiload.

 

Continue reading »]]>

Teradata database has become so popular today that you will often find many Teradata certification courses and online Teradata certification training resources.

To become an Teradata Certified Professional, you will need to learn all the Teradata certification test objectives. Most of us are concern about the practice or sample questions for various Teradata exams. These sample questions are beneficial in testing your knowledge and boost your confidence to appear for the real Exam.

I have collected few sample questions for the first exam of Teradata 12 certification i.e. TD Basic Exam (TEO-121)

There are 15 questions, all of them focused on the Basic Exam only.

So those interested in certification have a look on the questions and rate yourself before appearing in TD certification.

Click below link to start -

TD 12 Basic Exam (TEO-121) – Sample 2

You may also want to check below mentioned Sample Papers for completer reference -

TD 12 Basic Exam (TEO-121) – Sample 1

TD 12 SQL Exam (TEO-122) – Sample 1

I have also identified few E books which is immensely useful for Teradata Certification Preparation. Please have a look and if you are seriously preparing for TD certifications then don’t hesitate to purchase it.(Hover the mouse on the below image to get more information about the book)

Disclaimer – These questions are collected from various sources on internet and teradatatech is not claiming for their occurrence in the real teradata certification exams. Please treat these questions as the practice set and test your knowledge accordingly.

Continue reading »]]> Each table in Teradata is required to have a primary index.Even if you did not define any primary index in CREATE table statement, the Teradata system will automatically takes very first column of the table as the primary index. The primary index defines where data will reside and which AMP receives the row.

The three most important roles the primary index does is the following –

• Data Distribution
• Fastest way to retrieve Data
• Incredibly important for Joins

In short primary index provides the fastest physical path to retrieving data.

Two types of Primary Indexes are given below  –

Unique Primary Index (UPI)

A unique primary index means that the value for the selected column must be unique.  In the example below the EMP_ID is the Unique Primary Index.

A unique primary index (UPI) will always spread the rows of the table evenly amongst the AMPs.

Non Unique Primary Index (NUPI)

A Non unique primary index means that the value for the selected column can be non unique.  In the example below the LAST_NAME is the Non Unique Primary Index.

Key Point - Don’t assume that UPI is always the BEST CHOICE; sometimes the use of NUPI is very critical. If you need to perform lot of query with LAST_NAME in the WHERE clause then NUPI on last name is essential.

Primary Index and the ROW HASH


The primary index is the key to determine where the ROW of the table will reside on which AMP. When a new row arrive for insert in Teradata the following steps occur –

1. Teradata Parsing Engine (PE) examines the primary index of the row.
2. Teradata takes the primary index of the rows and run it through HASHING ALGORITHM.
3. The output of the Hashing Algorithm is the 32 bit Row – Hash value.

e.g –

New row coming with following attributes -

Hash the PI value -> 55 / HASHING ALGORITHM = 11110000111100001111000011110000 (Row – Hash)

This value in Binary format is called as the Row – Hash Value. And it will always be unique for the given PI. Means it’s not possible to get two Row – Hash Value for the same EMP_ID (55).

This 32 bit Row – Hash value determines in which AMP the row will reside and it always attached along with the ROW to make it a UNIQUE identification for that ROW.

Now in Teradata we have HASH MAP which contains the different bucket called as Hash Map Buckets. These buckets contain only the different AMP number which is attached with the Teradata system. Suppose that Teradata system contains 4 AMP then this Hash Map contains numbers from 1 to 4 in different buckets. see image below -

Four Amp Hash Map

The Row – hash value which we determine above,  is used to point to certain bucket in the Hash Map. This value points to only one bucket in the Hash Map and that bucket number will determine the AMP number where this new row will reside.

e.g. –

11110000111100001111000011110000 tells that it correspond to first row and fourth column of Hash Map i.e. AMP no = 4

So finally we have the AMP number (4) where the new row will reside.

After doing all this calculation in PE , the PE instruct BYNET to send the row along with its unique Row – Hash Value in the AMP number 4.

The new row along with its Row hash Value is send to AMP number 4 for storing. In this way we achieve the data distribution for each row of the given table.

Uniqueness value


When the AMP receives the row it will place the row into the proper table, and the AMP itself check that is there any other row with the same Row- Hash Value. If this is the first Row with the particular Row- Hash value then it will assign a uniqueness value of 1 along with the row. But if this is the second row with the same Row-Hash value for the same table then it will assign a uniqueness value of 2. Similarly it assign a value of 3 if it finds third occurrence of the same Row – Hash, in this fashion it keeps on adding uniqueness value for the duplicates rows.

If you don’t get this concept at the first go don’t worry, it is explained again with the suitable examples for more clarity.

An example of UPI table, where EMP_ID is the UPI.

Note – as the table has UPI defined on EMP_ID it will always has the unique row in the given table. So each time AMP will assign the uniqueness value of 1 against each row.

An example of NUPI table, where LAST_NAME is the NUPI.

Note – as the table has NUPI defined on LAST_NAME, we have three rows for the same LAST_NAME = ‘Kumar’. Hence the AMP will assign different uniqueness value from 1 t 3 for each row.

Together with Row – Hash and the uniqueness value the Teradata make as 64 bit ROW – ID to uniquely identify each row in the given AMP.

This is the complete reference for Primary Index in Teradata. I tried to make it as simple as possible for the newbies, in case of any correction or clarification feel free to reach  me

To read about Secondary index in teradata please go to – Secondary Index in Teradata

Continue reading »]]>

COLLECT STATS is one on the most useful utility in Teradata. It helps Parsing Engine (PE) to make an effective plan to execute query, so that less resource are utilized and performance is improvised.

But there is a serious confusion between doing COLLECT STATS on column level or table level.

Here i like to explain the difference between both the scenarios with appropriate example.

Suppose we have a table and we want to collect statistics on 4 columns. We can do this by the below mentioned query -

collect stats on TABLE_NAME column(COL1);
collect stats on TABLE_NAME column(COL2);
collect stats on TABLE_NAME column(COL3);
collect stats on TABLE_NAME column(COL4);

The other way of defining COLLECT STATS on the same table is -

collect stats on TABLE_NAME;

The second query is collecting stats on table level. Both the approach will do the same thing, but we cannot directly collect stats on  table level.

If you are collecting STATS on table level, then the STATS must already be defined on the above mentioned 4 columns of the table. This can be done at the time of creation of table. If we are not defining the STATS for the columns earlier, then our COLLECT STATS on table level will give an error message.

Collect stats on table can only be used on a table which has stats defined on it, on any no. of columns for that be. Once stats are defined on the columns you can you use collect stats on table for refresh the stats for all the defined columns. If you use collect stats on TABLE_NAME column(COL1) it will refresh the stats on the mentioned column (COL1) only.

We can say that COLLECT STATS on table level is just the shortcut of collecting stats on all the columns on whom we have already defined stats. It saves the overhead of writing COLLECT STATS query on each column, each time we want to gather statistics. In our case there are only 4 columns whose statistics we want, but suppose in a huge table if there are more than 10-20 columns which are required for COLLECT STATS, then our COLLECT STATS on table level saves us a lot of typing time. Its good practice to perform a collect stats on the columns of even an empty table, when data is loaded into the table a collect stats on table can be used to collect all the statistics without having to collect statistics on the individual columns.

But from a DBA point of view its always wise to collect stats on column level. Because if we are doing collect stats on TABLE level then stats are not committed on any column until all stats are collected. Since collecting stats required resources, so if you have huge data table then it might take a long time. If you need to free the resource then you have to abort the whole process, and no stats will be collected on any column.On the other hand if your are doing it on COLUMN level, and killed the process in between then atleast we have stats collected on columns which are already done before killing the script. And we can again start from that column where we left.

So these are the pros and cons of both the approach of collecting stats. Its according to the requirement of the project, which approach to choose