Archives of the TeradataForum

Message Posted: Wed, 04 Aug 2010 @ 14:10:00 GMT

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

Hi Paul,

Distribution of table across the AMPs is given by the following query:

- change databasename (DBC in the example) and tablename (RESUSAGESPMA in the example)

- one row returned

     SELECT (100 * AVG(currentperm) / MAX(currentperm)) AS ParfEff FROM dbc.tablesize WHERE databasename = 'dbc'
       AND tablename = 'resusagespma';

Perfect distribution is given by ParEff = 100%. If it's less than about 80 then this may be part of the problem.

How many AMPs on your system ? Use the following:

     SELECT COUNT(*) AS AmpCount
     FROM dbc.diskspace
     WHERE databasename = USER;

What release of TD are you on ? Use the following:

     SELECT *
     FROM dbc.dbcinfo;

The above info leads into the following notes:

- unless you're on a very small (typically development) TD systems, 180k rows is 'nothing'. This processing should run quickly - probably a few minutes, not "3 or 4 hours".

- strictly speaking, TD doesn't use a "primary key" (PK), it uses a "primary index" (PI) and the two are not the same. Think of a PK as a logical modelling term, must be unique, non-null, one and only one per table. A PI can be unique or non-unique (there are advantages to both), can contain NULL values (flame wars start over that topic !) and you can have at most one per table. Yes, TD supports the "primary key" syntax but it may do something slightly different and it may not affect distribution.

- prior to TD 13 there MUST be one and only one PI per table. With TD 13 there is the concept of a "NO PI" table, but in this case (I believe) the rows are round-robbined to the AMPs on teh system, tehrefore you'll get even distribution.

- so I doubt if adding a PK will help, UNLESS the table has no PI defined AND the first column in the table definition has very skewed data values AND (probably) you're on a TD release prior to TD 13.

Let's start with that lot and see where we get to.

Cheers,

Dave

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