Partition a Table

One of the most common table reorganization tasks is to partition a table that is currently not partitioned but that could benefit in manageability and/or performance by becoming partitioned. It may be that this table is a throwback from an earlier Oracle database version like those that were created long ago before partitioning was available or that it simply has grown over time to the point where partitioning makes sense. Another example might be that it is partitioned, but it is so by an older partitioning method or scheme. So if one wants to rebuild a hash partitioned table using Oracle 11g’s new interval partitioning, there are many other partitioning scenarios, but the basic idea is this: the table is currently not partitioned or partitioned incorrectly, and this needs to be remedied.

 

Return once again to the MOVIES demo schema and partition the CUSTOMER table. And like any real world database, the rest of the database design depends on the customer, such as there are foreign keys to it. Not only that, but CUSTOMER has additional indexes and triggers. Here is the complete DDL for CUSTOMER. So as can be seen, it is much more than just a simple standalone table since there are also indexes and triggers that go with this table.

 

 

 

  CREATE TABLE "MOVIES"."CUSTOMER"

  ( "CUSTOMERID" NUMBER(10,0) NOT NULL ENABLE,

    "FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE,

    "LASTNAME" VARCHAR2(30) NOT NULL ENABLE,

    "PHONE" CHAR(10) NOT NULL ENABLE,

    "ADDRESS" VARCHAR2(40) NOT NULL ENABLE,

    "CITY" VARCHAR2(30) NOT NULL ENABLE,

    "STATE" CHAR(2) NOT NULL ENABLE,

    "ZIP" CHAR(5) NOT NULL ENABLE,

    "BIRTHDATE" DATE,

    "GENDER" CHAR(1),

     CHECK (Gender in ('M','F')) ENABLE,

     CHECK (CustomerId > 0) ENABLE,

     CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CUSTOMERID")

     CONSTRAINT "CUSTOMER_UK" UNIQUE ("FIRSTNAME", "LASTNAME", "PHONE")

  );

 

  CREATE INDEX "MOVIES"."CUSTOMER_IE1" ON "MOVIES"."CUSTOMER" ("LASTNAME");

  CREATE INDEX "MOVIES"."CUSTOMER_IE2" ON "MOVIES"."CUSTOMER" ("PHONE");

  CREATE INDEX "MOVIES"."CUSTOMER_IE3" ON "MOVIES"."CUSTOMER" ("ZIP");

 

  CREATE OR REPLACE TRIGGER "MOVIES"."CUSTOMER_CHECKS"

  BEFORE INSERT OR UPDATE

  ON customer

  FOR EACH ROW

  declare

  -- Declare User Defined Exception

  bad_length  exception;

  pragma exception_init(bad_length,-20001);

  bad_date    exception;

  pragma exception_init(bad_date,-20002);

begin

  -- Check Values for Correct Length

  if (length(rtrim(:new.phone)) < 10 or

      length(rtrim(:new.state)) <  2 or

      length(rtrim(:new.zip))   <  5) then

    raise bad_length;

  end if;

  -- Check Dates for Reasonableness

  if (:new.birthdate > sysdate-18*365) then

    raise bad_date;

  end if;

  -- Force Values to All Upper Case

  :new.state  := upper(:new.state);

  :new.gender := upper(:new.gender);

exception

  when bad_length then

    raise_application_error(-20001, 'Illegal length: value shorter than required');

  when bad_date then

    raise_application_error(-20002, 'Illegal date: value fails reasonableness test');

end;

/

 

Step 1: Verify that the table is a candidate for online redefinition

This is a very easy step, but it is also a very critical step. If this step fails, then do not attempt to use dbms_redefinition to rebuild or redefine the table. Since it is known that customer has a primary key from reviewing the prior DD, then it can be verified that it can be used as the redefinition driver. Otherwise, redefinition must function utilizing the data’s ROWID. Remember, dbms_redefinition is simply using materialized views behind the scenes.

 

Step 2: Create an interim table

Assuming that the table is a valid candidate, the interim table can then be created. This will be the partitioned table for the demonstration scenario. Note that the CREATE TABLE AS SELECT (CTAS) method is being used to save time here. The rows are not actually being copied because the SELECT WHERE clause evaluates to false. This is just a relatively easy shorthand method for the copy and, of course, adding the partitioning clause.

 

 

Step 3: Enable parallel DML operations

Now for those on multi-processor database servers, parallel operations can be enabled for the session to speed up the redefinition process. This is an optional step, but generally worth considering. Just make sure not to overdo using parallelization. If there is a very fast I/O subsystem and nothing else is really running, then consider up to two or four times of the actual CPU core count. It would also be good to check the db_writers init.ora parameter as well because it should be more than one if the choice is to force massive parallel operations that require extensive I/O.  Here are the commands for this.

 

 

Step 4: Start the redefinition process

From this step forward, watch the time between steps. This means that the following steps need to happen in sequence and without major delays between them. This is pointed out because some DBAs are hesitant to put these reorganization steps in a script as they want to manually monitor each step of the process. That is fine, just do not go to lunch or home between them. If  everything is ready to proceed to completion, then start the redefinition process.

 

 

Step 5: Copy dependent objects

This step performs one of the most critical and easily forgotten steps if this process was done without dbms_redefition – to automatically create any required triggers, indexes, materialized view logs, grants, and/or constraints on the table. If one refers back to the section about DDL extraction via dbms_metadata, it is easy to guess that Oracle is eating their own cooking internally here. Now it makes a little more sense as to why dbms_metadata was designed as it is. So maybe the over-engineered comments referred to earlier by others on the web was premature or assumed through ignorance. Look how easy it is to copy all dependent objects with just a single call to dbms_redefition.

 

 

Step 6: Check for any errors

It is advisable now to check that this last operation completed successfully. This is stated because remember that it is doing quite a few things in the background here. It is quite possible for some things to need reviewing and possibly fixed manually. In most cases there should be no rows returned, so proceed.

 

 

Step 7: Synchronize the interim table (optional)

If there has been any activity or transaction between the start of the redefinition and now, it might be advisable to resynchronize the interim table one more time. When in doubt, it is very much like chicken soup here – it may not help, but it will not hurt anything either.

 

 

Step 8: Complete the redefinition

This step does two things: it severs the behind-the-scenes materialized view connection and swaps the data dictionary entries for the table and interim table. So now, what was the interim table is caught up on structural modifications and any data transactions. Thus, it is safe to make this data dictionary entry swap.

 

 

Step 9: Drop the interim table

The interim table is now famished which, as of the last step, is actually the original table via the dictionary entry swap done by the finish operation. So drop that table. And if there is a concern about the data, an option is to do a SELECT against the new original table to verify that nothing has been lost.

 

 

Finally, here is the screen for doing the same thing via OEM. As was said before, most folks will probably go with this. But at least it is now evident what is going on inside.