Archives of the TeradataForum
Message Posted: Tue, 04 Jan 2011 @ 12:15:25 GMT
| Subj: | | Re: Basic teradata questions |
| |
| From: | | DWellman |
Hi,
I've added some additional information for some answers.
Cheers,
Dave
Ward Analytics Ltd: Information in motion (www.ward-analytics.com)
| | "1.) in case of NUSI "After the row hash of SI value is calculated the bynet will automatically active all the amps as per parsing
engines instructions. each amp locates the sutable rows containing the qualifying values and rowhash. this subtable rows contain the rowid for the
base rows, WHICH ARE GAUREENTED TO BE ON THE SAME AMP AS THE SUB TABLE ROWS ....." my question is why should the rowid's present on the same amp?
they can be on other amps" | |
| | in case of NUSI the subtables are created on the same AMP that's why !! | |
-- DW this is just the way Teradata works. NUSI index rows are always stored on the same AMP as the data row.
| | "2.) this question may be silly but please answer me "if number of physical reads exceed the number of datablocks,then the optimizer
may decide that full table scan is faster" here what does it mean by physicalreads & datablocks" | |
| | physicalreads & datablocks -- physicalreads may stand for values distinct values, typical rows per value teradata table consist of several
data blocks whenever a row is added to the end of data block When data rows begin being loaded or inserted each AMP allocates at least a 512 byte
sector inside a disk cylinder. | |
| | This 512 byte sector is considered a block of data | |
-- DW: Not quite. A data block in Teradata is a multiple of 512 bytes. The number of physical reads may exceed the number f blocks if the
optimizer were to access the table via an index. In this case (and probably some others) the full table scan will be chosen as a 'cheaper'
option.
| | "3.) what are master indexes and cylinder indexes?" | |
| | Cylinder Indexes was allocated at the beginning of every cylinder Not sure about master indexes | |
-- DW: Master indexes. One per AMP, initially built when the DBMS starts up, maintained during maintenance. They provide pointers to the
Cylinder Indexes on that AMP.
| | "4.) does client attached network means loading client software on node?" | |
| | Mainframes system serve as client attached. | |
| | Unix based systems serve for network attached | |
-- DW: No. Network clients are lan Windows, Unix, Linux, AS400 etc clients. Basically anything except mainframes. If you have software
running on a node that is also network attached clients, but that doesn't often happen today.
| | "-how many primary indexes the table can contain?
-on how many coloumns does we can specify primary index?
-how many secondary indexes the table can contain?
-on how many coloumns does we can specify secondary index?
-maximum how many sessions does a P.E support?
-max how many vprocs can a clique support?" | |
As per v2r6 (64 columns in composite primary index)
32 columns for secondary index
120 sessions for PE
max how many vprocs can a clique support (that depends on node config )
| 
