Home Page for the TeradataForum
 

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023