In this post I have given an example how we can do partition of a table. In other post I will explain which type of partition we will choose and how we can improve partitioning performance etc.
STEP 1:
Create a temporary table with same definition of table, that is to be partitioned.
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 constraints
ALTER TABLE big_table2 ADD (
CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);
Create Index
CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;
Add foreign key,
ALTER TABLE big_table2 ADD (
CONSTRAINT bita_look_fk2
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
Step 2
If the existing table name is big_table then,
ALTER TABLE big_table2
EXCHANGE PARTITION big_table_2007
WITH TABLE big_table
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;
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 bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;
STEP 3:
Next, we split the single large partition into smaller partitions as required.Syntax is,
ALTER TABLE table_name
SPLIT PARTITION partition_name
AT range_definition
INTO (PARTITION first_partition, PARTITION second_partition)
UPDATE GLOBAL INDEXES;
For our example,
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;
STEP 4:
The following queries show that the partitioning was successful.
SELECT partitioned FROM user_tables WHERE table_name = 'BIG_TABLE';
SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'BIG_TABLE';
Dont be hopeless. Run the following query and then select user_tables.
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
STEP 5:
Merge,
ALTER TABLE table_name
MERGE PARTITIONS partition_name1,partition_name2
INTO PARTITION partition_name1 second_partition first_partition range_definition partition_name table_name ;
No comments:
Post a Comment