Forum: Building VoltDB Applications

Post: Which table to partition on in stored procedure

Which table to partition on in stored procedure
sramdram
Jan 18, 2016
I have a stored procedure that does a few things:

1. select from table a
2. insert into table b
3. insert into table c
4. delete from table a

Which table/column should the partitioning be defined on the stored procedure...(the concept of partitiolning a stored procedure if it does simple things like selecting/inserting from a single table makes sense..what happens if multiple tables are involved)
Anton
Feb 17, 2016
Hi sramdram,

I'm trying to resolve question which has relation to yours. What I understood:
For example I have three partitioned tables:
Table_1 - partition column col_1 (type INTEGER)
Table_2 - partition column col_2 (type INTEGER)
Table_3 - partition column col_3 (type INTEGER)

I have created partitioned stored procedure (SP) with parameter INTEGER and SP uses all three tables - Table_1, Table_2, Table_3.

If I use one type for partitioning column for several tables it means (I hope so) if Table_1 in partition 1 uses range from 1 to 100 for col_1 then Table_2 for col_2 uses the same range (1-100) in partition 1

As a matter of fact it means - if I use one data type for all partitioning columns for all tables I can use all such tables in one partitioned SP and parameter can be used for all tables in their partition columns.

I'm not sure is it true and I would like to make sure. :)

And in this case will be better to use such DDL construction

CREATE PROCEDURE FROM CLASS com.volt.test.TestProcedure;
PARTITION PROCEDURE TestProcedure ON TABLE Table_1 COLUMN col_1;
PARTITION PROCEDURE TestProcedure ON TABLE Table_2 COLUMN col_2;
PARTITION PROCEDURE TestProcedure ON TABLE Table_3 COLUMN col_3;

and VoltCompiler could check all records. Now it checks only one (last) line.