Forum: Building VoltDB Applications

Post: Do Stored Porcedures on top of Materialized Views need to be partitioned?

Do Stored Porcedures on top of Materialized Views need to be partitioned?
SharonDashet
Oct 15, 2015
Hi

As materialized View has implicit partitioning according to base table, does the stored procedure using the view has the same implicit partitioning?
I looked at the windowing example and seems like there is no need to partition them explicitly- can you validate it will work in a single partition mode although it does not include the partitioning clause?
bballard
Oct 15, 2015
Hi,

Stored procedures need to be explicitly partitioned in the DDL. This can be done with the PARTITION PROCEDURE syntax, or directly with CREATE PROCEDURE [PARTITION ON ...] ...

When running an Ad Hoc SQL query, the planner can identify when the query has an input parameter or constant value for the partitioning key, and it will automatically run the query in a single partition. However this automatic partitioning logic does not extend to procedures. Procedures can contain multiple SQL statements, and query multiple partitioned tables. For example, in cases where two partitioned tables are partitioned on the same value, the name of the column doesn't need to match, only the value put into the partitioning column should match. The database has no way to know these relationships when it is planning a procedure, but the developer knows, so this continues to be something the developer must explicitly state in the DDL.

Regards,
Ben