Saturday, September 14, 2013

Oracle: partitioning a huge table _online_

My customer has an application in production 24/24 with tables getting huge (billions of rows... )  we'd like to partition it to compress and move older data.

Not a problem -as long as we have disk space. With Oracle 10g/11g we can partition it online, without any interruption.

The package to use is DBMS_REDEFINITION, and the basic idea is:
-Create a new table how you like it (partitionned... )
-This table becomes a materialized view and is synchronized with the existing table (need double space!)
-Add indexes etc..
-When ready: Lock shortly the table while it does a last resync and switch the segments in the data dictionary

The segments formerly belonging to the MV now belongs to the new table, and it was completely transparent.

( I think in Oracle 12c, this is getting easier with a single command. -will check)



-- In this example we have a table: BIGAPP1.BIGTABLE


-- create the new table as we want it  (this will be transormed to a Materialized View)
drop table BIGAPP1.BIGTABLE_TMPPARTNING ;

create table BIGAPP1.BIGTABLE_TMPPARTNING
PARTITION BY RANGE (TIME_STAMP_UTC)

  PARTITION P2010 VALUES LESS THAN (TO_DATE('2011-01-01','YYYY-MM-DD' )) TABLESPACE TS_DATA110,
  PARTITION P2011 VALUES LESS THAN (TO_DATE('2012-01-01','YYYY-MM-DD' )) TABLESPACE TS_DATA110,
  PARTITION P2012 VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD' )) TABLESPACE TS_DATA110,
  PARTITION P2013 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD' )) TABLESPACE TS_DATA110,
  PARTITION P2014 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD' )) TABLESPACE TS_DATA110
) as select * from BIGAPP1.BIGTABLE where 0=1 ;


-- call dbms_redefintion
-- there are two methods, by PK or by ROWID which we had to use here
-- ref: Admin guide / chapter 15 Managing Tables / Redefining Tables Online

set serveroutput on;
exec dbms_output.put_line('check');

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
   uname        => 'BIGAPP1',
   tname        => 'BIGTABLE',
   options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- The following will actually copy the data to the materialized view, and add a temporary index (because of USE_ROWIS)
-- on the (slow) test system it took 25 minutes for 11M rows
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('BIGAPP1', 'BIGTABLE','BIGTABLE_TMPPARTNING',
       null, dbms_redefinition.cons_use_rowid);
END;
/

--  now our  table has become a MV, but has only been synced once (an INSERT at this point is not copied )
-- now we may create the indexes

-- local index
CREATE INDEX BIGAPP1.IX_BIGTABLE_AUDOBJID ON BIGAPP1.BIGTABLE_TMPPARTNING(SURV_OBJ_ID) LOCAL
( PARTITION P2010  TABLESPACE TS_DATA110, 
  PARTITION P2011  TABLESPACE TS_DATA110, 
  PARTITION P2012  TABLESPACE TS_DATA110, 
  PARTITION P2013  TABLESPACE TS_DATA110, 
  PARTITION P2014  TABLESPACE TS_DATA110 );
-- 3 minutes

...

-- a global index because it is unique and does not contain the partition key
-- we partition it with another range

CREATE UNIQUE INDEX BIGAPP1.IX_BIGTABLE_OBJID ON BIGAPP1.BIGTABLE_TMPPARTNING(OB_ID) GLOBAL PARTITION BY RANGE (OB_ID)

  PARTITION P10M VALUES LESS THAN (10000000) TABLESPACE TS_DATA110,
  PARTITION P20M VALUES LESS THAN (20000000) TABLESPACE TS_DATA110,
  PARTITION P30M VALUES LESS THAN (30000000) TABLESPACE TS_DATA110
  PARTITION PMAX VALUES LESS THAN (MAXVALUE) TABLESPACE TS_DATA110
) ;


--process grants, triggers, constraints and privileges
-- here we do not set the 3r param to CONS_ORIG_PARAMS, because we re-creates the index manually

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('BIGAPP1', 'BIGTABLE','BIGTABLE_TMPPARTNING', 
   0, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
-- 1 minute

--We can check for errors here, typically: already existing constraints
select object_name, base_table_name, ddl_txt from    DBA_REDEFINITION_ERRORS;

-- Optional re-synchro : I prefer to do that since it reduces the last operation, which is the only one doing some locking

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('BIGAPP1', 'BIGTABLE','BIGTABLE_TMPPARTNING');
END;
/

-- And right after this, call the final step
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('BIGAPP1', 'BIGTABLE','BIGTABLE_TMPPARTNING');
END;
/

-- 20 sec

--this final step did a lock, a final sync and switched the segments references in the data dictionary
-- BIGTABLE is now partitioned and  BIGTABLE_TMPPARTNING is actually pointing to the old table (including its data: we have a copy)

-- We may keep a little the older version just to verify everything is OK

-- Verify we do have data in different partitions now:

select count(*) from BIGAPP1.BIGTABLE partition (P2010);
select count(*) from BIGAPP1.BIGTABLE partition (P2011);
select count(*) from BIGAPP1.BIGTABLE partition (P2012);
select count(*) from BIGAPP1.BIGTABLE partition (P2013);


-- OK DONE!  




References:
Admin Guide - redefinition example
This example demonstrates redefining a single partition. It moves the oldest partition of a range-partitioned sales table to a tablespace named TBS_LOW_FREQ

No comments:

Post a Comment