Deep Thinking in Our Life

Create partitioned table.

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_2007 VALUES LESS THAN (MAXVALUE));

– Add new keys, FKs and 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)
);With this destination table in place we can start the conversion.

EXCHANGE PARTITION
We now switch the segments associated with the source table and the partition in the destination table using the EXCHANGE PARTITION syntax.

ALTER TABLE big_table2
EXCHANGE PARTITION big_table_2007
WITH TABLE big_table
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;The exchange operation should not be affected by the size of the segments involved.

Once this is complete we can drop the old table and rename the new table and all it’s constraints.

DROP TABLE big_table;
RENAME big_table2 TO big_table;

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;SPLIT PARTITION
Next, we split the single large partition into smaller partitions as required.

ALTER TABLE big_table
SPLIT PARTITION big_table_2007 AT (TO_DATE(’31-DEC-2005 23:59:59′, ‘DD-MON-YYYY HH24:MI:SS’))
INTO (PARTITION big_table_2005,
PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;

ALTER TABLE big_table
SPLIT PARTITION big_table_2007 AT (TO_DATE(’31-DEC-2006 23:59:59′, ‘DD-MON-YYYY HH24:MI:SS’))
INTO (PARTITION big_table_2006,
PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;

EXEC DBMS_STATS.gather_table_stats(USER, ‘BIG_TABLE’, cascade => TRUE);The following queries show that the partitioning was successful.

SELECT partitioned
FROM   user_tables
WHERE  table_name = ‘BIG_TABLE’;

PAR
—
YES

1 row selected.

SELECT partition_name, num_rows
FROM   user_tab_partitions
WHERE  table_name = ‘BIG_TABLE’;

PARTITION_NAME                   NUM_ROWS
—————————— ———-
BIG_TABLE_2005                     335326
BIG_TABLE_2006                     332730
BIG_TABLE_2007                     334340

3 rows selected.