Archives of the TeradataForum

Message Posted: Thu, 13 Jan 2011 @ 14:24:19 GMT


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

Subj:		Re: Small Table on One AMP

From:		Dieter Noeth

Irfan Hanif wrote:

I was reading somewhere that table with fewer rows, should be placed on one AMP. So the data can accessed using single AMP read operation.

I wanted to know how can we accomplish it? How to place table on one AMP and what the potential advantages that we can gain or any disadvantages ?

It's done adding a dummy column as PI, e.g.

     create table mytinytable
     ( dummy byteint not null default 1 check (dummy = 1),
        other columns
     ) primary index (dummy);

In the view layer you access it using

     create view myview as
     select
        other columns
     from mytinytable
     where dummy = 1;

This might be usefull on large systems with lots of AMPs when the table is heavily accessed and always "duplicated on all AMPs" in Explain.

It's using less resources (e.g. worker tasks), because only a single AMP reads all the rows and distributes them using a single broadcast to all AMPs (1:n) vs. all AMPs access, but only a few find a row and broadcast it to all other AMPs (n:n).

Dieter


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

Attachments

Library

Quick Reference