Description

Partitioning a stored procedure means that the procedure executes within a unique partition of the database. The partition in which the procedure executes is chosen at runtime based on the table and column specified by table-name and column-name and the value of the first parameter to the procedure. For example:

PARTITION TABLE Employees ON COLUMN BadgeNumber;
PARTITION PROCEDURE FindEmployee ON TABLE Employees COLUMN BadgeNumber;

The procedure FindEmployee is partitioned on the table Employees, and table Employees is in turn partitioned on the column BadgeNumber. This means that when the stored procedure FindEmployee is invoked VoltDB determines which partition to run the stored procedure in based on the value of the first parameter to the procedure and the corresponding partitioning value for the column BadgeNumber. So to find the employee with badge number 145303 you would invoke the stored procedure as follows:

clientResponse response = client.callProcedure("FindEmployee", 145303);

By default, VoltDB uses the first parameter to the stored procedure as the partitioning value. However, if you want to use the value of a different parameter, you can use the PARAMETER clause. The PARAMETER clause specifies which procedure parameter to use as the partitioning value, with position specifying the parameter position, counting from zero. (In other words, position 0 is the first parameter, position 1 is the second, and so on.)

The specified table must be a partitioned table and cannot be an export stream or replicated table.

You specify the procedure by its simplified class name. Do not include any other parts of the class path. Note that the simple procedure name you specify in the PARTITION PROCEDURE may be different than the class name you specify in the CREATE PARTITION statement, which can include a relative path. For example, if the class for the stored procedure is mydb.procedures.FindEmployee, the procedure name in the PARTITION PROCEDURE statement should be FindEmployee:

CREATE PROCEDURE FROM CLASS mydb.procedures.FindEmployee;
PARTITION PROCEDURE FindEmployee ON TABLE Employees COLUMN BadgeNumber;

Examples

The following example declares a stored procedure, using an inline SQL query, and then partitions the procedure on the Customer table, Note that the PARTITION PROCEDURE statement includes the PARAMETER clause, since the partitioning column is not the first of the placeholders in the SQL query. Also note that the PARTITION argument is zero-based, so the value "1" identifies the second placeholder.

CREATE PROCEDURE GetCustomerByName AS
    SELECT *  from Customer WHERE FirstName=? AND  LastName = ?
    ORDER BY LastName, FirstName, CustomerID;

PARTITION PROCEDURE GetCustomerByName 
    ON TABLE Customer COLUMN LastName
    PARAMETER 1;

The next example declares a stored procedure as a Java class. Since the first argument to the procedure's run method is the value for the LastName column, The PARTITION PROCEDURE statement does not require a POSITION clause and can use the default.

CREATE PROCEDURE FROM CLASS org.mycompany.ChangeCustomerAddress;

PARTITION PROCEDURE ChangeCustomerAddress 
    ON TABLE Customer COLUMN LastName;