The last blog, here, discussed different single-level partitioning strategies. This blog will explain strategies to make non-partitioned tables partitioned tables. The objects created will be stored in the HR schema. First, a table needs to be created (this is the same table that was created in the last blog) and a unique index will be put on the column CUSTOMER_ID:

CREATE TABLE hr.sales
(
  customer_id NUMBER,
  sell_id     NUMBER,
  product_id  NUMBER,
  sold_date   DATE,
  price       NUMBER,
  quantity    NUMBER,
  state       VARCHAR(5)
);

  CREATE UNIQUE INDEX hr.customer_id_u ON hr.sales(customer_id);

A few rows need to be inserted into the table:

INSERT INTO hr.sales 
  VALUES(1, 10, 15, TO_DATE('20110110', 'YYYYMMDD'), 100, 5, 'CA');

INSERT INTO hr.sales 
  VALUES(2, 15, 10, TO_DATE('20110116', 'YYYYMMDD'), 25, 2, 'OR');

INSERT INTO hr.sales 
VALUES(3, 20, 5, TO_DATE('20110210', 'YYYYMMDD'), 10, 3, 'WA');

Many times, non-partitioned tables are created that are meant to store a small amount of data. The data stored in these tables can eventually grow making the table perform inefficiently. A strategy that can be used is to partition the table. Transforming a non-partitioned table into a partitioned table has to be done manually. Two ways to do this will be discussed in this blog: using Create Table As Select (CTAS) and using Oracle’s provided package DBMS_REDEFINITION.

Create Table As Select (CTAS)

Create Table As Select (CTAS) will create a new partitioned table with the same column names and data as the original SALES table created. The SALES table needs to be altered to READ ONLY in order to guarantee data consistency:

ALTER TABLE hr.sales READ ONLY;

Now CTAS can be used to move the data from the SALES table into a partitioned table:

CREATE TABLE hr.sales_list_partition 
  PARTITION BY LIST (state)
    (PARTITION west VALUES ('CA','OR','WA'),
     PARTITION default_partition VALUES (DEFAULT)) 
AS
SELECT * FROM hr.sales;

In order to have this new partitioned table named the same as the non-partitioned table, the non-partitioned table has to be renamed or dropped. The newly created partitioned table can now be named the same as the non-partitioned tables:

ALTER TABLE hr.sales
  RENAME TO sales_old;

ALTER TABLE hr.sales_list_partition
RENAME TO sales;

The old SALES table can now be altered back to READ WRITE

ALTER TABLE hr.sales_old READ WRITE;

This is a quick way to alter a non-partitioned table into a partitioned table, but the partitioned table does not have the same dependencies as the non-partitioned. For example, the new table does not have the same indexes the non-partitioned table had. The indexes must be manually created on the new table:

CREATE UNIQUE INDEX new_customer_id_u ON hr.sales(customer_id);

DBMS_REDEFINITION

DBMS_REDEFINITION is an Oracle provided package that can transform non-partitioned tables into partitioned tables. In order to do this, there has to be an interim table created. This table will be partitioned by range:

CREATE TABLE ht.sales_interim
(
  customer_id NUMBER,
  sell_id     NUMBER,
  product_id  NUMBER,
  sold_date   DATE,
  price       NUMBER,
  quantity    NUMBER,
  state       VARCHAR(5)
)
PARTITION BY RANGE (sold_date)
  (PARTITION P_JANUARY VALUES LESS THAN (TO_DATE('20110131', 'YYYYMMDD')),
    PARTITION P_FEBRUARY VALUES LESS THAN (TO_DATE('20110228', 'YYYYMMDD')),
    PARTITION max_value VALUES LESS THAN (MAXVALUE)
    );

Once an interim table is created, the procedure START_REDEF_TABLE from the DBMS_REDEFINITION package can be executed. The first parameter is the name of the schema where the tables are stored (this example uses the HR schema), the second parameter is the name of the original table, the third parameter is the name of the interim table, and the fourth parameter indicates the type of redefinition to use. This example uses DBMS_REDEFINITION.CONS_USE_ROWID since there is no primary key on the original table:

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE
   (uname          => 'HR',
    orig_table     => 'SALES_OLD',
    int_table      => 'SALES_INTERIM',
    options_flag   => dbms_redefinition.cons_use_rowid);
END;
/
Now, the dependent objects from the original table can be applied to the interim table using the COPY_TABLE_DEPENDENTS procedure. The first three parameters are the same as START_REDEF_TABLE, but the fourth parameter will store the number of errors that occur during the copy. A variable needs to be declared that will hold the number of errors returned:

VARIABLE num_errors NUMBER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
   (uname        => 'HR',
    orig_table   => 'SALES_OLD',
    int_table    => 'SALES_INTERIM',
    num_errors   => :num_errors);
END;
/

To check if there were any errors, the following command can be executed:

PRINT num_errors;

While Oracle is copying the table’s data and its dependents, any changes to the original table, or “master” table, are stored in a materialized view. This allows Oracle to guarantee that any data changed in the master table during the copying process will also be changed in the interim table.

The last step is to execute the procedure FINISH_REDEF_TABLE. This procedure could take a long time depending on the number of changes to the master table. A way to speed up this process is to use the procedure SYNC_INTERIM_TABLE before executing FINISH_REDEF_TABLE. This procedure will apply all the changes stored in the materialized view to the interim table:

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE
   (uname       => 'HR',
    orig_table  => 'SALES_OLD',
    int_table   => 'SALES_INTERIM');
END;
/

The final step is to execute the FINISH_REDEF_TABLE procedure:

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE
   (uname       => 'HR',
    orig_table  => 'SALES_OLD',
    int_table   => 'SALES_INTERIM');
END;
/

This final procedure will switch the names of the tables so that SALES_OLD now has the definition of the partitioned table. If any changes were made to the master table before executing FINISH_REDEF_TABLE, they will be applied to the interim table from the materialized view. The master table is locked during this procedure to guarantee data consistency. This is a great way to convert a non-partitioned table into a partitioned table while online because the DBMS_REDEFINITION package performs the manual tasks that were required in the CTAS example (such as creating the new index or renaming the table) automatically. Also, end users will have no idea that this redefinition took place. To them, everything will appear normal.

Link to script that contains the examples in this post.

Comment

2012-01 – Converting Non-Partitioned Tables to Partitioned Tables

HEADQUARTERS