Archives of the TeradataForum

Message Posted: Thu, 01 Dec 2011 @ 19:46:57 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

There is typically no direct link between a session and an amp. You start your program and logon to the Teradata system. This creates a 'session' on Teradata. This session is your connection to the teradata system and any queries that you run are run under the control f this session. This session is NOT linked to an AMP. When the queries run they use one or more amps.

You have probably come across the term "primary index" (PI). This is how we control the distribution of data across the amps in the system. Typically you want the data in your table to be evenly distributed across all amps in the system (it won't be perfect, but it needs to be good enough).

Use the following partial table definition as an example:

     CREATE TABLE Customer
     (CustomerID
     ,First_Name
     ,Last_Name
     ,Address_1
     ,Address_2
     ,PostCode
     .
     .
     .
     )
     UNIQUE PRIMARY INDEX(CustomerID);

Now use the following queries as examples:

     SELECT * FROM CUSTOMER WHERE CUSTOMERID = 1234;

This query is run on a single amp because the query has an equality constraint on all columns in this table's PI and so the optimizer knows which AMP this row is stored on - if it exists. This query will be very quick, only using one or two IO's to retrieve the data.

     SELECT * FROM CUSTOMER WHERE CUSTOMERID = 57;

This query is also run on a single amp because the query has an equality constraint on all columns in this table's PI. Assume that the values 1234 and 57 are stored on different AMPs, then this query will use a different amp compared ot the first query.

     SELECT * FROM CUSTOMER WHERE LAST_NAME = 'SMITH';

This query will use all AMPs in the system and may 'take some time' as it has to scan all rows in the table, selecting those rows that meet the selection criteria.

Each of the above queries will run under the same session, but they will use a different number of amps and/or different amps.

Hope that helps,

Dave

Ward Analytics Ltd - Information in motion (www.ward-analytics.com)

	<Prev	Next>		<<First	<Prev	Next>	Last>>