Distibution of Small table (rows less than number of amps) to single amp for Performance

Database
Enthusiast

Distibution of Small table (rows less than number of amps) to single amp for Performance

There was an old technique where you would make tables with number of rows less than the number of amps distirbute to a singel amp.

You would do this by creating a PI_column and populate it with the same value on all rows.   This was done so that you would not have to "interrupt" all amps on the system when using the table.   Can anyone point me to documentation on this technique?   Does this technique still add any value?

Tags (1)
2 REPLIES
Teradata Employee

Re: Distibution of Small table (rows less than number of amps) to single amp for Performance

If the system has a very large number of AMPs, this technique can remove all-AMP steps from the query plans which access the table. If the queries that access the table are even medium size, the difference will not be noticeable/measurable. But if large numbers of tactical queries are using the table without PI access, then it may be valuable.

The technique is simple, add a column to the table and make that column the NUPI. Put the same value in that column on every row. And make certain to not forget the last step - everywhere the table is referenced, add a where clause with "single_AMP_PI=<value>". Probably best to make a view with that condition and always reference the view. Without the condition, the access will still be all-AMP because it will not be known that the table has data on only one.

And cool future - be sure to read about "sparse MAPs" when TD16 hits shelves near you!

Enthusiast

Re: Distibution of Small table (rows less than number of amps) to single amp for Performance

Todd,

Thanks for the reply.   Exactly what I wanted to confirm that I is still valid on current architecture.