All Forums Database
RobertDiehl 7 posts Joined 05/10
02 Aug 2016
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?

Thanks, Bob Diehl
ToddAWalter 316 posts Joined 10/11
03 Aug 2016

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!

RobertDiehl 7 posts Joined 05/10
03 Aug 2016

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


Bob Diehl

You must sign in to leave a comment.