Tutorial for Swapping Partitions

The following example shows how to create two partitioned tables and then swap certain partitions between the tables.

Both tables have the same definition and have partitions for various year values. You swap the partitions where year = 2008 and year = 2009. Both tables have at least two rows that will be swapped.

  1. Create the customer_info table:

    => CREATE TABLE customer_info (
          customer_id INT PRIMARY KEY NOT NULL,
          first_name VARCHAR(25),
          last_name VARCHAR(35),
          city VARCHAR(25),
          year INT NOT NULL)
          ORDER BY last_name
          PARTITION BY year;
  2. Insert data into the customer_info table:

    => INSERT INTO customer_info VALUES (1, 'Joe', 'Smith', 'Denver', 2008);
    => INSERT INTO customer_info VALUES (2, 'Bob', 'Jones', 'Boston', 2008);
    => INSERT INTO customer_info VALUES (3, 'Silke', 'Muller', 'Frankfurt', 2007);
    => INSERT INTO customer_info VALUES (4, 'Simone', 'Bernard', 'Paris', 2014);
    => INSERT INTO customer_info VALUES (5, 'Vijay', 'Kumar', 'New Delhi', 2010);
    
  3. View the table data:

    => SELECT * FROM customer_info;
    customer_id | first_name | last_name |   city    | year
    ------------+------------+-----------+-----------+------
              1 | Joe        | Smith     | Denver    | 2008
              2 | Bob        | Jones     | Boston    | 2008
              3 | Silke      | Muller    | Frankfurt | 2007
              4 | Simone     | Bernard   | Paris     | 2014
              5 | Vijay      | Kumar     | New Delhi | 2010
  4. Create a second table, member_info, that has the same definition as customer_info:

    => CREATE TABLE member_info (
    customer_id INT PRIMARY KEY NOT NULL,
    first_name VARCHAR(25),
    last_name VARCHAR(35),
    city VARCHAR(25),
    year INT NOT NULL)
    ORDER BY last_name
    PARTITION BY year;

  5. Insert data into the member_info table:

    => INSERT INTO member_info VALUES (1, 'Jane', 'Doe', 'Miami', 2001);
    => INSERT INTO member_info VALUES (2, 'Mike', 'Brown', 'Chicago', 2014);
    => INSERT INTO member_info VALUES (3, 'Patrick', 'OMalley', 'Dublin', 2008);
    => INSERT INTO member_info VALUES (4, 'Ana', 'Lopez', 'Madrid', 2009);
    => INSERT INTO member_info VALUES (5, 'Mike', 'Green', 'New York', 2008);
  6. View the data in the member_info table:

    => SELECT * FROM member_info;
    customer_id | first_name | last_name | city | year
    -------------+------------+-----------+----------+------
    1 | Jane | Doe | Miami | 2001
    2 | Mike | Brown | Chicago | 2014
    3 | Patrick | OMalley | Dublin | 2008
    4 | Ana | Lopez | Madrid | 2009
    5 | Mike | Green | New York | 2008
  7. To swap the partitions, run the SWAP_PARTITIONS_BETWEEN_TABLES function:

    => SELECT SWAP_PARTITIONS_BETWEEN_TABLES('customer_info',2008,2009,'member_info');
  8. To verify that the partitions have been swapped, query the contents of both tables, and confirm the following results:

    • After the swap, the rows in both tables whose year values are 2008 or 2009 have moved to the other table.
    • The partitions that contain the rows for Ana Lopez, Mike Green, and Patrick OMalley moved from member_info to customer_info.
    • The partition that contains the rows for Joe Smith and Bob Jones moved from customer_info to member_info.
    => SELECT * FROM customer_info;
    customer_id | first_name | last_name | city | year -------------+------------+-----------+-----------+------ 4 | Simone | Bernard | Paris | 2014 5 | Vijay | Kumar | New Delhi | 2010 3 | Silke | Muller | Frankfurt | 2007 4 | Ana | Lopez | Madrid | 2009 5 | Mike | Green | New York | 2008 3 | Patrick | OMalley | Dublin | 2008
    => SELECT * FROM member_info;
    customer_id | first_name | last_name | city | year -------------+------------+-----------+---------+------ 2 | Bob | Jones | Boston | 2008 1 | Joe | Smith | Denver | 2008 2 | Mike | Brown | Chicago | 2014 1 | Jane | Doe | Miami | 2001