CODEWITHSUNDEEP
oracle-databaseoracle10gpartitioning
Kevin Babcock
Kevin Babcock

Reputation: 10247

How can I remove table partitions from an Oracle table?

I've discovered that the partitions used on a particular table are hurting query performance and would like to remove the partitions from the table. Is there an easy way to do this? The table in question has 64 partitions. Based on some initial investigation, I've come up with the following options. Is there a better way?

  1. Copy data into another table, drop all partitions, then copy the data back into the original table
  2. Copy data into another table, drop the original table, then rename the new table and rebuild the indexes
  3. Use MERGE PARTITION to merge all partitions into a single partition

Thoughts?

Upvotes: 3

Views: 20557

Answers (2)

Tvitmsvleli
Tvitmsvleli

Reputation: 247

Partitioning the table, can hurt query performance, but also it can improve performance... It depends on design of your technical solution, using correct indexes, hints and etc...

If you want to remove partitions from your table without downtime and invalidating other objects which are using your table, you can use dbms_redefinition which will analyse and move indexes as you configure/plan your redefinition process, you can configure your table's structure.

--1. Verify object for Redefinition
--2. Create Partitioned Interim Table
--3. Start the Redefinition Process
--4. Copy Dependent Objects
--5. Synchronize the Interim Table
--6. Complete the Redefinition Process

--Step 1: Verification
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA', 'YOUR_CURRENT_TABLE');
END;

--Step 2: Create Partitioned Interim Table
--sql....
create table SCHEMA.YOUR_FUTURE_TABLE
(
  id                        NUMBER(10),
  some_col                  VARCHAR2(100)
);

--Step 3: Redefinition
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname      => 'SCHEMA',
    orig_table => 'YOUR_CURRENT_TABLE',
    int_table  => 'YOUR_FUTURE_TABLE'
  );
END;

--Step 4: Copy Keys
DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname            => 'SCHEMA',
    orig_table       => 'YOUR_CURRENT_TABLE',
    int_table        => 'YOUR_FUTURE_TABLE',
    num_errors       => num_errors, 
    copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => FALSE, 
    copy_statistics  => TRUE,  
    copy_mvlog       => FALSE  
  );
  IF num_errors > 0 THEN
    DBMS_OUTPUT.PUT_LINE('There are errors: ' || num_errors);
  END IF;
END;

--Step 5: Synchronize Archive
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
    uname      => 'SCHEMA',
    orig_table => 'YOUR_CURRENT_TABLE',
    int_table  => 'YOUR_FUTURE_TABLE'
  );
END;

--Step 6: Complete Redefinition 
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
    uname      => 'SCHEMA',
    orig_table => 'YOUR_CURRENT_TABLE',
    int_table  => 'YOUR_FUTURE_TABLE'
  );
END;

This will move your data from tables without invalidating other objects and there is no need for any downtime.

Upvotes: 0

APC
APC

Reputation: 146349

Personally I think a variant on option 2 sounds like the best approach.

  1. Create new table as select * from old table
  2. Build indexes on new table (*)
  3. Rename old table to something different
  4. Rename new table to old table name
  5. Switch any foreign key constraints from old table to new table. Also transfer any grants, synonyms, etc.
  6. Drop old table in slow time

(*) A non-partitioned table will probably require different indexes from the partitioned one. If you are particularly attached to the index names you can use ALTER INDEX ... RENAME TO ... syntax after step 6.

The advantage of this approach is that it minimises your downtime (basically steps 3, 4 and 5).

Upvotes: 5

Related Questions