How Teradata Retrieves data from Database?

Basically Parser Engine(PE) have some planes to retrieve the data from database.
1st) Single AMP operation: This is the fastest way to retrieve the data. In this PE will check for data in single AMP only. It will happen when we are using Primary Index value in Where condition.
2nd) Two AMP operation: This is 2nd fastest way to retrieve the data. in this PE will Check 2 amps for retrieving the data. It will Happen when we are using Secondary Index value in where condition.
3rd) ALL AMP operation but not full table scan, in this PE will check particular RANGE or CASE of data. It will happen when we are using Partition's based on primary index values.
4th) ALL AMP operation and full table scan. It will happen when we are not using where condition or in where condition if you used Non-indexed column. at that time PE will go for Full table scan.

Retrieving a row by way of the Primary Index: 

Think we have a table EMP, in that table we have columns like Enum,Ename,Sal...in that 'Enum' is Primary index.

ENUM	ENAME	SAL
1001	XXXXXX	35000
1002	YYYYYY	32000

Now we will retrieving data from EMP table:
SELECT ENUM,SAL FROM EMP WHERE ENUM=1001;
in this case:

• PE will take that primary index value and apply hash formula on that value.
• By using row hash value, That will find the 'Hash bucket' value.
• In that 'Hash bucket', we will get 'Hash amp' value.
• So PE will go directly to that AMP, and retrieve the data from that table.

In this,why i am telling Single AMP operation means, by using '1001' 'row hash' value, PE will go directly where row is located.

Suppose in that Same query we don't have any where conditions, PE will go for ALL AMP operation..

SELECT * FROM EMP;

In this case PE will check every AMP for that data..

Remaining 2nd,3rd,4th we will discuss coming post's.. Just remember those points.



Thanks for visiting my blog.. if you have any doubt's please let me know.. Give me your valuable comments..
Thanks in advance.
By Santhosh.B