I am new to teradata and reading the Teradata Architecture. I understand the concept of nodes and AMP. But I am unbale to figure out, when I am running an SQL Query, how does the Physical Bynet decide which Node to go to for the processing(assuming there are 2 or more nodes in my teradata instance). Also, can my data reside in mutiple nodes. I understand that there will be disks attached to the nodes.
Grateful for an elaborate explanation on this.
It's all about the Primary Index. Teradata uses a fixed hash map to determine where to put each row, based upon the hash result of the primary index column(s).
The parsing engine uses the query and it's knowledge of the data, including distribution and statistics to determine the least cost execution plan to resolve the query.
The bynet handles communication but doesn't play a part in determining what form that communication is, all-amp, group-amp, single-amp, etc...
Data in a table is spread across the amps via the PI hash map. The measure of data storage balance across the amps is called data skew. While a lot of new teradata developers latch on to the idea that the PI should uniquely identify every row, guaranteeing even data distribution, in a lot of instances that can hurt query performance.
The Primary index is NOT the same as a primary key. You will find a lot of tables in a robust Teradata system that have Non-Unique Primary Index (NUPI)and a Unique Secondary Index (USI).
The NUPI is designed to define the most common access path to the table. Yes distribution skew is a concern, after all it's a parallel environment and we want to maximise the use of it's resources. There are various 'rules of thumb' for acceptable data skew, I generally try to adhere to 25% or less.
You are correct, the disk arrays are attached to the nodes, multiple nodes attached to the same disk arrays are called cliques.
Even though the arrays are attached to the nodes, each amp controls its' own logical unit (lun) of disk space. Logical units allow each amp to process the work assigned to it in parallel with the other amps. LUNs aren't shared across amps. Arrays are shared across nodes to enable a hot standby node to seamlessly step in and replace a failed node with minimal performance impact.
The number of rows will impact your distribution skew, especially for very small tables on a large system, e.g. a table of states in the USA will have 50 rows, on a moderate TD system may have ~200 amps, you will have at best 150 amps without a row, there aren't enough rows to put one on each amp.
I Second Blaine on the PI.
When a table is created on Teradata platform, all the amps in the system will have the header information of the table which is not accessible to users except internal to Teradata(you can say BYNET). When a query is fired, the BYNET looking at the header will be able to identify the data on the amps and will retrieve it from the disks. if you fire the query below for an emty table still it will show that some space is consumed on all the amps and this is the space consumed by the header of the table.
sel * from dbc.tablesize where tablename='<tablename>' and databasename=''databasename>';
This will give the tables header stored on each amp and will also give the number of amps on the system. These AMPS are located on all the nodes which are currently active in the system(there are systems where Hot stand By nodes will be present).
the Bynet doesn't look at the table header, it simply does a lookup in the Hashmap.
Each system has a so-called Hashmap which is specifically created (during sysinit) for that system and determines the AMP on which a row is stored. This Hashmap is is an array of 1048576 values, each value is the number of an AMP.
SELECT HASHAMP(HASHBUCKET(HASHROW(value))) returns the AM responsible for value.