Partitioning an Existing Table in 11g
2 ways :- Using DBMS_REDEFINITION package
- Using EXCHANGE PARTITION syntax
EXCHANGE PARTITION
- Create a Sample Schema
- Create a Partitioned Destination Table
- EXCHANGE PARTITION
- SPLIT PARTITION
Create
a Sample Schema
First we create a sample schema as
our starting point.
--
Create and populate a small lookup table.
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;
--
Create and populate a larger table that we will later partition.
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;
/
--
Apply some constraints to the table.
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)
);
--
Gather statistics on the schema objects
EXEC
DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);
EXEC
DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
Create
a Partitioned Destination Table
Next we create a new table with the
appropriate partition structure to act as the destination table. The
destination must have the same constraints and indexes defined.
--
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 –- new table
EXCHANGE PARTITION big_table_2007 –- partition
name on new table
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; -- original table
RENAME
big_table2 TO big_table; -- new table to original 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.
Partitioning an Existing Table in 12C
In
previous releases you could partition a non-partitioned table using EXCHANGE PARTITION or DBMS_REDEFINITION in an "almost online" manner.
Oracle
Database 12c Release 2 makes it easier than ever to convert a non-partitioned
table to a partitioned table, requiring only a single command and no downtime
- Setup
- Partition a Table
- Composite Partition (Sub-Partition) a Table
- Restrictions
Setup
Create and populate a test table.
You will need to repeat this between each test.
DROP
TABLE t1 PURGE;
CREATE
TABLE t1 (
id
NUMBER,
description
VARCHAR2(50),
created_date DATE,
CONSTRAINT t1_pk PRIMARY KEY (id)
);
CREATE
INDEX t1_created_date_idx ON t1(created_date);
INSERT
INTO t1
SELECT
level,
'Description for ' || level,
ADD_MONTHS(TO_DATE('01-JAN-2017',
'DD-MON-YYYY'), -TRUNC(DBMS_RANDOM.value(1,4)-1)*12)
FROM dual
CONNECT
BY level <= 10000;
COMMIT;
We can see the data is spread across
three years.
SELECT
created_date, COUNT(*)
FROM t1
GROUP
BY created_date
ORDER
BY 1;
CREATED_D COUNT(*)
---------
----------
01-JAN-15 3340
01-JAN-16 3290
01-JAN-17 3370
SQL>
Partition
a Table
We can convert the table to a
partitioned table using the ALTER
TABLE ... MODIFY command.
Here are some basic
examples of this operation. Adding the ONLINE keyword allows the operation to be
completed online.
--
Basic offline operation.
ALTER
TABLE t1 MODIFY
PARTITION BY RANGE (created_date) (
PARTITION t1_part_2015 VALUES LESS THAN
(TO_DATE('01-JAN-2016','DD-MON-YYYY')),
PARTITION t1_part_2016 VALUES LESS THAN
(TO_DATE('01-JAN-2017','DD-MON-YYYY')),
PARTITION t1_part_2017 VALUES LESS THAN
(TO_DATE('01-JAN-2018','DD-MON-YYYY'))
);
--
Online operation.
ALTER
TABLE t1 MODIFY
PARTITION BY RANGE (created_date) (
PARTITION t1_part_2015 VALUES LESS THAN
(TO_DATE('01-JAN-2016','DD-MON-YYYY')),
PARTITION t1_part_2016 VALUES LESS THAN
(TO_DATE('01-JAN-2017','DD-MON-YYYY')),
PARTITION t1_part_2017 VALUES LESS THAN
(TO_DATE('01-JAN-2018','DD-MON-YYYY'))
) ONLINE;
--
Online operation with modification of index partitioning.
ALTER
TABLE t1 MODIFY
PARTITION BY RANGE (created_date) (
PARTITION t1_part_2015 VALUES LESS THAN
(TO_DATE('01-JAN-2016','DD-MON-YYYY')),
PARTITION t1_part_2016 VALUES LESS THAN
(TO_DATE('01-JAN-2017','DD-MON-YYYY')),
PARTITION t1_part_2017 VALUES LESS THAN
(TO_DATE('01-JAN-2018','DD-MON-YYYY'))
) ONLINE
UPDATE INDEXES
(
t1_pk GLOBAL,
t1_created_date_idx LOCAL
);
After running the last example we
can see the new partitions for the table and partitioned index.
COLUMN
table_name FORMAT A20
COLUMN
partition_name FORMAT A20
SELECT
table_name, partition_name
FROM user_tab_partitions
ORDER
BY 1,2;
TABLE_NAME PARTITION_NAME
--------------------
--------------------
T1 T1_PART_2015
T1 T1_PART_2016
T1 T1_PART_2017
SQL>
COLUMN
index_name FORMAT A20
COLUMN
partition_name FORMAT A20
SELECT
index_name, partition_name, status
FROM user_ind_partitions
ORDER
BY 1,2;
INDEX_NAME PARTITION_NAME STATUS
--------------------
-------------------- --------
T1_CREATED_DATE_IDX T1_PART_2015 USABLE
T1_CREATED_DATE_IDX T1_PART_2016 USABLE
T1_CREATED_DATE_IDX T1_PART_2017 USABLE
SQL>
Composite
Partition (Sub-Partition) a Table
The original table can also be
composite-partitioned using the ALTER TABLE ... MODIFY
command. In this example we convert the original table to a range-hash
partitioned table.
ALTER
TABLE t1 MODIFY
PARTITION BY RANGE (created_date)
SUBPARTITION BY HASH (id)(
PARTITION t1_part_2015 VALUES LESS THAN
(TO_DATE('01-JAN-2016','DD-MON-YYYY')) (
SUBPARTITION t1_sub_part_2015_1,
SUBPARTITION t1_sub_part_2015_2,
SUBPARTITION t1_sub_part_2015_3,
SUBPARTITION t1_sub_part_2015_4
),
PARTITION t1_part_2016 VALUES LESS THAN
(TO_DATE('01-JAN-2017','DD-MON-YYYY')) (
SUBPARTITION t1_sub_part_2016_1,
SUBPARTITION t1_sub_part_2016_2,
SUBPARTITION t1_sub_part_2016_3,
SUBPARTITION t1_sub_part_2016_4
),
PARTITION t1_part_2017 VALUES LESS THAN
(TO_DATE('01-JAN-2018','DD-MON-YYYY')) (
SUBPARTITION t1_sub_part_2017_1,
SUBPARTITION t1_sub_part_2017_2,
SUBPARTITION t1_sub_part_2017_3,
SUBPARTITION t1_sub_part_2017_4
)
) ONLINE
UPDATE INDEXES
(
t1_pk GLOBAL,
t1_created_date_idx LOCAL
);
The sub-partitions of the table and
partitioned index can be displayed using the following queries.
COLUMN
table_name FORMAT A20
COLUMN
partition_name FORMAT A20
COLUMN
subpartition_name FORMAT A20
SELECT
table_name, partition_name, subpartition_name
FROM user_tab_subpartitions
ORDER
BY 1,2, 3;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
--------------------
-------------------- --------------------
T1 T1_PART_2015 T1_SUB_PART_2015_1
T1 T1_PART_2015 T1_SUB_PART_2015_2
T1 T1_PART_2015 T1_SUB_PART_2015_3
T1 T1_PART_2015 T1_SUB_PART_2015_4
T1 T1_PART_2016 T1_SUB_PART_2016_1
T1 T1_PART_2016 T1_SUB_PART_2016_2
T1 T1_PART_2016 T1_SUB_PART_2016_3
T1 T1_PART_2016 T1_SUB_PART_2016_4
T1 T1_PART_2017 T1_SUB_PART_2017_1
T1 T1_PART_2017 T1_SUB_PART_2017_2
T1 T1_PART_2017 T1_SUB_PART_2017_3
T1 T1_PART_2017 T1_SUB_PART_2017_4
SQL>
COLUMN
index_name FORMAT A20
COLUMN
partition_name FORMAT A20
COLUMN
subpartition_name FORMAT A20
SELECT
index_name, partition_name, subpartition_name, status
FROM user_ind_subpartitions
ORDER
BY 1,2;
INDEX_NAME PARTITION_NAME SUBPARTITION_NAME STATUS
--------------------
-------------------- -------------------- --------
T1_CREATED_DATE_IDX T1_PART_2015 T1_SUB_PART_2015_1 USABLE
T1_CREATED_DATE_IDX T1_PART_2015 T1_SUB_PART_2015_2 USABLE
T1_CREATED_DATE_IDX T1_PART_2015 T1_SUB_PART_2015_3 USABLE
T1_CREATED_DATE_IDX T1_PART_2015 T1_SUB_PART_2015_4 USABLE
T1_CREATED_DATE_IDX T1_PART_2016 T1_SUB_PART_2016_1 USABLE
T1_CREATED_DATE_IDX T1_PART_2016 T1_SUB_PART_2016_2 USABLE
T1_CREATED_DATE_IDX T1_PART_2016 T1_SUB_PART_2016_4 USABLE
T1_CREATED_DATE_IDX T1_PART_2016 T1_SUB_PART_2016_3 USABLE
T1_CREATED_DATE_IDX T1_PART_2017 T1_SUB_PART_2017_1 USABLE
T1_CREATED_DATE_IDX T1_PART_2017 T1_SUB_PART_2017_3 USABLE
T1_CREATED_DATE_IDX T1_PART_2017 T1_SUB_PART_2017_2 USABLE
T1_CREATED_DATE_IDX T1_PART_2017 T1_SUB_PART_2017_4 USABLE
SQL>
Restrictions
There are some restrictions
associated with this functionality.
- It can't be used to partition an index-organized table (IOT).
- It can't be used if the table has a domain index.
- You can only convert a table to a reference-partitioned child table in offline mode.
Converting a Non-Partitioned Table to a Partitioned Table
MODIFY clause added to the ALTER TABLE
SQL statement. In addition, the keyword ONLINE
can be specified, enabling concurrent DML operations while the conversion is
ongoing.The following is an example of the
ALTER
TABLE statement using the ONLINE
keyword for an online conversion to a partitioned table.
Using the MODIFY clause of ALTER TABLE to
convert online to a partitioned table
ALTER TABLE employees_convert MODIFY
PARTITION BY RANGE (employee_id) INTERVAL (100)
( PARTITION P1 VALUES LESS THAN (100),
PARTITION P2 VALUES LESS THAN (500)
) ONLINE
UPDATE INDEXES
( IDX1_SALARY LOCAL,
IDX2_EMP_ID GLOBAL PARTITION BY RANGE (employee_id)
( PARTITION IP1 VALUES LESS THAN (MAXVALUE))
);
Considerations When Using the UPDATE INDEXES Clause
UPDATE INDEXES clause, note the following.
· This
clause can be used to change the partitioning state of indexes and storage
properties of the indexes being converted.
· The
specification of the
UPDATE INDEXES clause is optional.
Indexes are maintained both for the online and
offline conversion to a partitioned table.
· This
clause cannot change the columns on which the original list of indexes are
defined.
· This
clause cannot change the uniqueness property of the index or any other index
property.
· If
you do not specify the tablespace for any of the indexes, then the following
tablespace defaults apply.
· Local
indexes after the conversion collocate with the table partition.
· Global
indexes after the conversion reside in the same tablespace of the original
global index on the non-partitioned table.
· If
you do not specify the
INDEXES
clause or the INDEXES clause
does not specify all the indexes on the original non-partitioned table, then
the following default behavior applies for all unspecified indexes.
· Global
partitioned indexes remain the same and retain the original partitioning shape.
· Non-prefixed
indexes become global nonpartitioned indexes.
· Prefixed
indexes are converted to local partitioned indexes.
Prefixed means that the partition key columns are
included in the index definition, but the index definition is not limited to
including the partitioning keys only.
· Bitmap
indexes become local partitioned indexes, regardless whether they are prefixed
or not.
Bitmap indexes must always be local partitioned
indexes.
· The
conversion operation cannot be performed if there are domain indexes.
No comments:
Post a Comment