@SwapTables

Documentation

VoltDB Home » Documentation » Using VoltDB

@SwapTables

@SwapTables — Swaps the contents of one table for another

Synopsis

@SwapTables String[] table-name, String[] table-name

Description

The @SwapTables system procedure swaps the contents of one table for another. So, for example, if table A has 2 rows and table B has 10 rows, after executing the following system procedure call table A would have 10 rows and table B would have 2 rows:

sqlcmd> exec @SwapTables 'A' 'B';

The tables being swapped must have identical schema. That is the names, datatype, and order of the columns must be the same and the tables must have the same indexes and other constraints. Also there cannot be any views on either table. If these requirements are not met, or if either of the named tables does not exist, the system procedure returns an error.

The system procedure provides a significant performance advantage over any comparable SQL statements when swapping large tables because the operation does not actually move any data. The pointers for the two tables are switched, eliminating any need for excessive temporary storage or data movement.

When using database replication (DR), you must pause the database before using the @SwapTables procedure, or the system procedure returns an error. The process for using @SwapTables in a DR environment is the following:

  • When using passive DR:

    1. Pause the master database with voltadmin pause --wait.

    2. Invoke @SwapTables on the master database.

    3. Resume the master database with voltadmin resume.

    4. Invoke the same @SwapTables call on the replica.

  • When using XDCR:

    1. Pause all of the clusters in the XDCR relationshp with voltadmin pause --wait.

    2. Invoke the same @SwapTables call on all of the databases.

    3. Resume all the databases with voltadmin resume.

Return Values

Returns one VoltTable with one row and one column.

NameDatatypeDescription
MODIFIED_TUPLESBIGINTThe number of tuples affected by the swap. In other words, the sum of the tuples in both tables.

Examples

The following example uses the @SwapTables system procedure to replace a lookup table of hot topics with an updated list in a single statement.

sqlcmd> exec @SwapTables Hot_Topics Hot_Topics_Update;