In dit artikel een simpele methode om met DBMS_REDEFINITION een bestaande tabel te partitioneren.

Maak een voorbeeld schema
Maak een gepartitioneerde interim tabel
Start het Redefintion Proces
maak Constraints en Indexen
Maak het redefinition Proces af

MAAK EEN VOORBEELD SCHEMA

Eerst maken we een voorbeeld schema dat als startpunt kan dienen.

Maak en vul een kleine smal 'lookup' tabel.

CREATE TABLE lookup (
  id            NUMBER(10),
  description   VARCHAR2(50)
);
ALTER TABLE lookup ADD (
  CONSTRAINT lookup_pk PRIMARY KEY (id)
);
INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;

Maak en vul een een grotere tabel die we later zullen partitioneren.

CREATE TABLE big_table (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
);
DECLARE
  l_lookup_id    lookup.id%TYPE;
  l_create_date  DATE;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    IF MOD(i, 3) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -24);
      l_lookup_id   := 2;
    ELSIF MOD(i, 2) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -12);
      l_lookup_id   := 1;
    ELSE
      l_create_date := SYSDATE;
      l_lookup_id   := 3;
    END IF;
    
    INSERT INTO big_table (id, created_date, lookup_id, data)
    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
  END LOOP;
  COMMIT;
END;
/

Leg een aantal constraints op de tabel

ALTER TABLE big_table ADD (
  CONSTRAINT big_table_pk PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i ON big_table(created_date);
CREATE INDEX bita_look_fk_i ON big_table(lookup_id);
ALTER TABLE big_table ADD (
  CONSTRAINT bita_look_fk
  FOREIGN KEY (lookup_id)
  REFERENCES lookup(id)
);

Verzamel statistieken van het schema

EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);

MAAK EEN GEPARTITIONEERDE INTERIM TABEL

Nu maken we een nieuwe tabel met de geschikte partitie structuur, die als interin tabel kan fungeren.

Maak de gepartitioneerde tabel.

CREATE TABLE big_table2 (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),
 PARTITION big_table_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
 PARTITION big_table_2005 VALUES LESS THAN (MAXVALUE));

Met de interim tabel kunnen we de online redefinition starten.

START HET REDEFINITION PROCES

Eerst controleren of redefinition mogelijk is met het volgende commando:

EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'BIG_TABLE');

Als er geen fouten worden gegeven, is het veilig om de redefenition te starten met het volgende commando:

BEGIN
  DBMS_REDEFINITION.start_redef_table(
    uname      => USER,        
    orig_table => 'BIG_TABLE',
    int_table  => 'BIG_TABLE2');
END;
/

Afhankelijk van de grootte van de tabel, kan dit enige tijd duren.

MAAK CONSTRAINTS EN INDEXEN

Als er tussen het completen van de vorige actie en de uitvoer van de komende actie enige vertraging zit is het wellicht verstandig om de interim tabel voor het maken van de constraints en indexen, eerst te synchroniseren met het volgende commando:

BEGIN
  dbms_redefinition.sync_interim_table(
    uname      => USER,        
    orig_table => 'BIG_TABLE',
    int_table  => 'BIG_TABLE2');
END;
/

De constraints en indexen van de originele moeten toegepast worden op de interim tabel met gebruikt van alternatieve namen om fouten te voorkomen. De indexen zouden gemaakt moeten worden met het juiste partioning schema waar het voor gebruikt gaat worden.

Het toevoegen van nieuwe Keys, Foreign Keys en Triggers.

ALTER TABLE big_table2 ADD (
  CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;
ALTER TABLE big_table2 ADD (
  CONSTRAINT bita_look_fk2
  FOREIGN KEY (lookup_id)
  REFERENCES lookup(id)
);

Verzamel statistieken voor de nieuwe tabel.

EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE2', cascade => TRUE);

MAAK HET REDEFINITION PROCES AF

Als de constraints en indexen gemaakt zijn, kan de redefenition afgemaakt worden met het volgende commando:

BEGIN
  dbms_redefinition.finish_redef_table(
    uname      => USER,        
    orig_table => 'BIG_TABLE',
    int_table  => 'BIG_TABLE2');
END;
/

Op dit moment is de interim tabel de "echte" tabel en hun namen zijn omgewisseld in de data dictionary. Het enige wat overeblijft is wat opruim acties:

Verwijder de originele tabel die nu de naam van de interim tabel heeft.

DROP TABLE big_table2;

Hernoem alle constraints en indexen zodat ze overeenkomen met de originele namen.

ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;

De volgende queries laten zien dat het partitioneren is gelukt

SELECT partitioned
FROM   user_tables
WHERE  table_name = 'BIG_TABLE';
PAR
---
YES
1 row selected.
SELECT partition_name
FROM   user_tab_partitions
WHERE  table_name = 'BIG_TABLE';
PARTITION_NAME
------------------------------
BIG_TABLE_2003
BIG_TABLE_2004
BIG_TABLE_2005
3 rows selected.

TOP

ZIE OOK

PARTITIONEREN MET EXCHANGE PARTITION
GEPARTITIONEERDE TABELLEN EN INDEXEN - ORACLE 10G