Part 1  |  Part 2

There are various ways to convert a table into a partitioned one, from the simple export/import to the sophisticated online redefinition in Oracle9i. Each option is unique in some way — and one could be perfect for your specific situation. In part one of this article, I explained some of those options. In part two of this article, we will explore the rest of the methods and the relative merits and demerits of them so that you can decide on their suitability at your site. In two of my previous articles, “Perils and Pitfalls in Partitioning — Part 1” and “Perils and Pitfalls in Partitioning — Part 2,” I also explored some pitfalls and potential problems that you should be aware of while designing and maintaining a partitioning scheme.

Apart from the already documented and known partitioning methods, this article also explains another method developed by the author, which is undocumented. In case the table has to be available for changes during the conversion process, the article describes a way to facilitate that.

Previously, we explored five ways to convert a table into a partitioned table. In this article, we will continue with the sixth one.

Method 6: Online Redefinition

Oracle 9i has introduced a new package, dbms_redefinition, to reorganize tables online. A table can be changed without taking away access from the users. Under the hood, dbms_redefinition uses the Materialized View technology to convert the tables. This feature can be exploited to convert a non-partitioned table to a partitioned one. There are several restrictions on how this package can be used, however; for example, the table should not have a snapshot log (or materialized view log) defined on it, should not have columns of datatype BFILE, LONG, and so on.

It is much easier to convert a table using online redefinition when the table has a primary key. If there is no primary key, some non-default parameters must be passed to complete the definition process. To better explain the general concepts, I'll assume that the table has no primary key.

First, check to see whether the table can be reorganized using dbms_redefinition by issuing the following:

begin
    dbms_redefinition.can_redef_table (
    'SCOTT',
    'MYTAB',
    dbms_redefinition.cons_use_rowid);
end;

If the table has a primary key, use the following syntax instead.

begin
    dbms_redefinition.can_redef_table (
        'SCOTT',
        'MYTAB');
end;

Note the use of the fixed value dbms_redefinition.cons_use_rowid, which instructs the package to use only the rowid as the mechanism to test. If the table can be reorganized with the package, the above command does not return anything. If there is a reason why dbms_redefinition can't be used to reorganize the table, the package throws out a self-explanatory error.

Assuming that the redefinition can be done, the next step is to create the target table as follows:

CREATE TABLE MYTAB_NEW
PARTITION BY RANGE (COL1)
(
    PARTITION P1 VALUES LESS THAN (101) TABLESPACE TS_P1,
    PARTITION P2 VALUES LESS THAN (201) TABLESPACE TS_P2,
    PARTITION P3 VALUES LESS THAN (301) TSBALESPACE TS_P3,
    PARTITION PM VALUES LESS THAN (MAXVALUE) TABLESPACE TS_PM
)
AS
SELECT * FROM MYTAB
WHERE 1=2;

This creates an empty table in the form we want it to be. Next, it's time to start the redefinition process. Issue the following:

begin
    dbms_redefinition.start_redef_table (
        'SCOTT',
        'MYTAB',
        'MYTAB_NEW',
        null,
        dbms_redefinition.cons_use_rowid
    );
end;

Note the use of NULL as the fourth parameter. This parameter is for mapping columns of the original table to the new table, if they are different. Since we are not changing the column names, this parameter is left to null. The last parameter instructs the package that the table does not have a primary key and the rowid should be used to redefine. If there is a primary key, you can leave this parameter as the default value dbms_redefinition.cons_use_pk.

Behind the scenes, Oracle creates several objects to facilitate the conversion — for instance, a materialized view log is created on the table MYTAB, a trigger is created to populate the log, a materialized view is created using the prebuilt table MYTAB_NEW, and so on. (Note: Since a materialized view log is created on the table MYTAB, the table should not have a view log before the process starts.)

While Oracle creates these objects, the source table is fully accessible and the changes are captured in the MV log. From time to time, you should use the following command to synchronize the table and materialized view.

begin
    dbms_redefinition.sync_interim_table (
        'SCOTT',
        'MYTAB',
        'MYTAB_NEW'
    );
end;

When the sync-up operations are complete and you have reached a relatively quite time, finish off the synchronization process.

begin
    dbms_redefinition.finish_redef_table (
        'SCOTT',
        'MYTAB',
        'MYTAB_NEW'
    );
end;

This operation does the final sync-up of the materialized view with the main table, drops the MV logs, etc. and renames the main table to the target table. While doing the final step, it holds a lock on the table momentarily, restricting access. That is why you should do it at a relatively quiet time.  Since the finish process also does a sync up, that step can be shortened if the sync ups are done prior to calling the procedure. That is the reason for calling the sync_interim_table() procedure several times. The interim table MYTAB_NEW can then be dropped.

Advantages of Online Redefinition

The immediate advantage of this option is clear: — it can be done online. Apart from the brief moment when the final synchronization is done, the table MYTAB is fully accessible.

Online redefinition sounds great, but should you automatically use it if you have Oracle9i? Not necessarily.

Disadvantages of Online Redefinition

The biggest disadvantage is the space requirement. You'll need space equivalent to twice the size of the entire table, plus space for the MV logs, and so on. So, space requirements for online redefinition might be a constraint in some cases.

Another restriction placed on the table is that it should not have an MV log. This can render many tables ineligible for online redefinition, particularly in DW and replication environments, where there is the greatest need to convert to a partitioned table.

If you are dealing with a large table, the MV log can be substantially large, affecting performance. And since these are created in your default tablespace, which may also contain the parent table, sometimes there may be contention at the disk level for access.

Typically, with large snapshots, the efficient method for conversion is to create a table first using direct load insert, then to use that table to create the snapshot with the "prebuilt table" clause. Since the snapshot is created automatically by Oracle and the user has no control over it, the initial population cannot take advantage of such load reducing features. Therefore, the initial load could severely strain resources like rollback segments, temporary tablespaces, and so on. And the rollback segment usage may trigger ORA-1555 errors.

To summarize, if you use the online redefinition method of conversion

• you can't take advantage of the capability to convert the table partition by partition, and
• you must have Oracle9i.

So, this method should only be used when the database absolutely cannot be inaccessible at all. However, if you choose this option, make sure that you have plenty of disk space and rollback segment space, and try to do it during a relatively quiet time.

Method 7: Split-Split

This method is not documented by Oracle, but was devised by the author when all of the other methods were not viable because of the constraints of the system. All of the previously described methods require enough space to be available to hold at least the biggest partition in the system. While that generally might not be a problem, under certain circumstances, the space may not be available to achieve the objective. However, the split-split method uses no additional space.

First, create the partitioned table with only one partition, the maxvalue partition. Next, split this partition at the lowest boundary point and repeat the process until all the partitions are created. Let's see how this is done using the same example we cited previously.

First, create a table called MYTAB_NEW as follows:

create table MYPART_NEW
(
    col1    number,
    col2    varchar2(10),
    col3    char(2)
)
NOLOGGING
partition by range (col1)
(
    partition pm values less than (maxvalue)
    tablespace TS_PM_DATA
);

Note that, even though our target table has several partitions, only the maximum value partition has been defined in this table. Although the table is defined, it does not contain any significant space at this time. The table is empty and contains only the minimum number of extents as defined for the tablespace TS_PM_DATA.

Next, create the indexes and constraints as seen in the table MYTAB:

CREATE INDEX IN_PART
ON PART (COL2) LOCAL
NOLOGGING;

ALTER TABLE PART
ADD CONSTRAINT CK_PART_01
CHECK (COL3 IS NOT NULL);

Next, we will exchange the table MYTAB with this partition

ALTER TABLE PART EXCHANGE PARTITION PM
WITH TABLE NOPART INCLUDING INDEXES;

This statement swaps the table's partition PM with the table MYTAB. The contents of MYTAB are now in the PM partition, and the MYTAB table is empty. Since this operation merely changes the data dictionary and doesn't physically move data, it doesn't generate redo and is extremely quick. The clause INCLUDING INDEXES swaps the indexes too,(i.e., the partition of the local index IN_MYTAB now contains the index information).

Next, split this single partition, starting with the lowest boundary (partition P1).

ALTER TABLE PART SPLIT PARTITION PM AT (101)
INTO (PARTITION P1, PARTITION PM);

This command creates a new partition called P1, and moves the rows with a COL1 value of less than 101 into this from PM. Since the table is defined as NOLOGGING, this doesn't generate much redo. After this operation, the partition PM contains data for the partitions other than P1. You should repeat this splitting process, but now with P2 in mind:

ALTER TABLE PART SPLIT PARTITION PM AT (201)
INTO (PARTITION P2, PARTITION PM);

This should be repeated until the partition PM is split up to P4, to the last partition but one. Since the index is defined as LOCAL, it will have been split, too, along with the table partition splitting command.

At the completion of the process, you will have a table called MYTAB_NEW with all the data from MYTAB and with the same indexes and constraints. You should now drop the table MYTAB and rename the table MYTAB_NEW to MYTAB so that applications will be able to access this table. Also, you should restore the privileges associated with NOPART to PART.

However, renaming table does not rename the constraints or indexes. Although applications may not be affected by the new name of the index and constraints, it may be necessary to change the names to avoid confusion. The names are changed by the following statements:

ALTER INDEX IN_PART RENAME TO IN_NOPART;
ALTER TABLE NOPART RENAME CONSTRAINT
CK_PART_01 TO CK_NOPART_01;

The latter command is available in Oracle9i only. If you are using V8i and cannot do this, you could drop the constraint from the MYTAB table first, then re-create the constraint with NOVALIDATE option.

A slight variation of this method is to create the index and constraints at the very end of the process. This makes the splitting process quite fast, since there is no splitting of the indexes.

Advantages

By eliminating the need to have two copies of the data simultaneously, no additional space for the tables is consumed; the space is carved out from the original table. In space-starved environments, this could be a plus.

Also, when the data is moved from one partition to the other, a little but not-so-insignificant side effect is that the data is defragmented.

Finally, if the tablespaces for all the partitions are the same as that of the original table, then the splitting does not physically move data between files. This significantly reduces redo generation and load on the I/O subsystem.

Disadvantages

The partition split operation physically moves the data from one partition to the other. This is extremely time and resource consuming as compared to direct load insert method. Redo generation is minimized, not suppressed completely and the size of the redo is significantly larger than in case of the direct load insert method.

It needs the table to be inaccessible for the entire duration of the operation, even for read only tasks.

Method 8: Copy

This approach uses the SQL*Plus command COPY to copy rows from one table to the other. Usually, COPY is used to copy rows across databases over db links, but this option can be used in this case as well.

First, create the target table MYTAB_NEW with the full partitioning structure, then copy the rows from the source table MYTAB.

A word of caution here: the command COPY has few friends at Oracle Corporation. There's been no enhancements since Oracle8 and may be deprecated in future releases. At least in 9i, this command is available.

COPY FROM SCOTT/TIGER@MYDB -
INSERT MYTAB_NEW -
USING SELECT * FROM MYTAB

At this time, several important points are worth noting. First, COPY is a command in SQL*Plus, like CONNECT, SHOW, and so on; it's not a SQL command like SELECT. Therefore, you must use it inside SQL*Plus only and not inside a stored procedure.

Second, note the use of a hyphen at the end of each line. Since COPY is a SQL*Plus command, the command is expected to end after a carriage return. However, since our command is actually in three lines, we have indicated the continuation of the line using the hyphen.

So, what does COPY provide that the other methods don't? It is the support for LONG datatype. If your tables contain LONG datatype, you can't use Create Table As Select (CTAS) or Direct Load Insert to copy the table rows. COPY is the only option in addition to Export/Import.

By default, COPY commits only after the end of the process. However, this can be controlled using variables inside the SQL*Plus environment:

• ARRAYSIZE : This number determines how many rows will be fetched in a single attempt.
• COPYCOMMIT: This number determines after how many batches (each of ARRAYSIZE number of rows) the commit should occur.

These variables are set by the SET command in SQL*Plus. So, if ARRAYSIZE is 1000 and COPYCOMMIT is 100, a commit occurs at the end of every 100,000 records. Unfortunately, the transaction cannot be made to produce less redo data or suppress generation of undo data, unlike NOLOGGING or Direct Load Insert. Nevertheless, by making a judicious decision on these two parameters, a commit frequency can be obtained in such a way that the rollback segments are not filled up, and the excessive commits don't happen either.

After the table rows are copied, you can drop the original table, rename the table MYTAB_NEW to MYTAB, and then recreate the indexes and constraints.

Advantages

The only key advantage for the COPY method is its support for LONG. Obviously, if your table does not have this datatype, this is no longer an advantage.

Tables can be converted partition by partition. Temporary part tables can be created and populated as per the code below, and the space requirement will be less than a full table size.

    COPY FROM SCOTT/TIGER@MYDB -
    INSERT MYTAB_NEW -
    USING SELECT * FROM MYTAB -
    WHERE COL2 BETWEEN 101 AND 200

Note the use of a predicate after the USING clause, for filtering the rows.

Disadvantages

The only supported datatypes are DATE, NUMBER, CHAR, VARCHAR2, and LONG. If you have LOBs or other Oracle object types, you can't use the COPY method.

Other disadvantages include the fact that

• the redo generation can't be suppressed
• the rollback segment requirement is huge, so ORA-1555 errors are possible
• the space requirement is twice the table size or at least twice that of the biggest partition

These disadvantages may make the COPY method seem worthless, but in key cases in which some LONG columns are present, this option will be useful.

Conversion in an Active Database

All these methods described above, with the exception of the Oracle9i Online Redefinition, rely on stopping all transactions during the conversion to a partitioned table. If the transactions are not stopped, the changes that occur from start to finish of the process are lost when the target table is renamed to the original table. Either the tablespaces should be made read-only, or the database should be brought down and brought up in read-only mode before starting the conversion process. This also alleviates the problem of running out of Rollback Segment space as well as the likelihood of ORA-1555 errors.

However, if it is not possible to stop the updates to this table during conversion, the setup needs to be modified. First, identify the primary key columns of the table. If the table does not have any primary key, it is advisable to establish some surrogate keys to identify a row uniquely. Make sure that the columns identified are not nullable. In the following example, assume the column is COL1, then create a table to hold changes to the table:

CREATE TABLE MYTAB_CHANGES
(
CHANGE_TYPECHAR(1) NOT NULL,
COL1_VALUE NUMBER(10) NOT NULL,
CHANGE_DATEDATE NOT NULL
);

Define a trigger on the table MYTAB as follows to capture changes to the table MYTAB:

CREATE OR REPLACE TRIGGER TR_MYTAB_CHANGES
AFTER INSERT, DELETE or UPDATE ON MYTAB
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO MYTAB_CHANGES
        VALUES ('I',:NEW.COL1,SYSDATE);
    ELSIF DELETING THEN
        INSERT INTO MYTAB_CHANGES
        VALUES ('D',:NEW.COL1,SYSDATE);
    ELSIF UPDATING THEN
        INSERT INTO MYTAB_CHANGES
        VALUES ('U',:NEW.COL1,SYSDATE);
    ELSE
       RAISE_APPLICATION_ERROR (-20001,'Wrong Operation');
    END IF;
END;

This trigger places the changes (new records, deleted records, and updates) to the changes table. After the operation of creating the target table is over, further changes are to be disallowed, and the target table is updated from the join of this changes table and the main source table. (Note: the code segment given below is for demonstration purpose only; you should provide an extensive error handling facility before deploying in production.)

BEGIN
    FOR CHGREC IN (
        SELECT * FROM MYTAB_CHANGES
        ORDER BY CHANGE_DATE) LOOP
        IF (CHGREC.CHANGE_TYPE = 'I') THEN
            BEGIN
                INSERT INTO MYTAB_NEW
                SELECT * FROM MYTAB
                WHERE COL1 = CHGREC.COL1_VALUE;
            EXCEPTION
                WHEN DUP_VALUE_ON_IND THEN
                    NULL;
            END;
        IF (CHGREC.CHANGE_TYPE = 'D') THEN
            BEGIN
                DELETE MYTAB_NEW
                WHERE COL1 = CHGREC.COL1_VALUE;
                IF (SQL%NOTFOUND) THEN
                    NULL;
                END IF;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    NULL;
            END;
            IF (CHGREC.CHANGE_TYPE = 'I') THEN
                BEGIN
                    DELETE MYTAB_NEW
                    WHERE COL1 = CHGREC.COL1_VALUE;
                    INSERT INTO MYTAB_NEW
                    SELECT * FROM MYTAB
                    WHERE COL1 = CHGREC.COL1_VALUE;
                EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                        NULL;
                    WHEN OTHERS THEN
                        RAISE;
                END;
            END IF;
        END IF;
    END LOOP;
END;

This script will update the rows of MYTAB_NEW with the data from the changes table. After this process is over, the data in the table MYTAB_NEW will be identical to the data in the table MYTAB.

Conclusion

Converting the table from partitioned to non-partitioned structure is a process that consumes considerable time and effort, and DBAs managing different sites have different constraints to deal with when making this executing this process. This two-part article presents several options for the DBA to make this process work with the hope that a specific option may be suitable based on the particular nature of the problem at the site. The author will appreciate if any feedback on this article and experiences on using these approaches are shared with him at arup@proligence.com.

--

Arup Nanda has been an Oracle DBA for 10 years. He is the founder of Proligence, Inc., a New York-area company that provides specialized Oracle database services. He is also an editor of SELECT, the journal of the International Oracle User Group. He has written Oracle technical articles in several national and international journals like SELECT, Oracle Scene, SQLUPDATE and presented in several conferences like Oracle Technology Symposium, Atlantic Oracle Training Conference, IOUG Live! among others. Send feedback to Arup about this article at arup@proligence.com. Based on the feedback, an updated copy of this article can be found at www.proligence.com.