Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Modify HUGE HASH partition table to RANGE partition and HASH subpartition

60716Mar 31 2006 — edited Apr 3 2006
I have a table with 130,000,000 rows hash partitioned as below

----RANGE PARTITION--
CREATE TABLE TEST_PART(
C_NBR CHAR(12),
YRMO_NBR NUMBER(6),
LINE_ID CHAR(2))
PARTITION BY RANGE (YRMO_NBR)(
PARTITION TEST_PART_200009 VALUES LESS THAN(200009),
PARTITION TEST_PART_200010 VALUES LESS THAN(200010),
PARTITION TEST_PART_200011 VALUES LESS THAN(200011),
PARTITION TEST_PART_MAX VALUES LESS THAN(MAXVALUE)
);
CREATE INDEX TEST_PART_IX_001 ON TEST_PART(C_NBR, LINE_ID);

Data: -
INSERT INTO TEST_PART
VALUES ('2000',200001,'CM');
INSERT INTO TEST_PART
VALUES ('2000',200009,'CM');
INSERT INTO TEST_PART
VALUES ('2000',200010,'CM');
VALUES ('2006',NULL,'CM');
COMMIT;

Now, I need to keep this table from growing by deleting records that fall b/w a specific range of YRMO_NBR. I think it will be easy if I create a range partition on YRMO_NBR field and then create the current hash partition as a sub-partition.

How do I change the current partition of the table from HASH partition to RANGE partition and a sub-partition (HASH) without losing the data and existing indexes?

The table after restructuring should look like the one below

COMPOSIT PARTITION-- RANGE PARTITION & HASH SUBPARTITION --
CREATE TABLE TEST_PART(
C_NBR CHAR(12),
YRMO_NBR NUMBER(6),
LINE_ID CHAR(2))
PARTITION BY RANGE (YRMO_NBR)
SUBPARTITION BY HASH (C_NBR) (
PARTITION TEST_PART_200009 VALUES LESS THAN(200009) SUBPARTITIONS 2,
PARTITION TEST_PART_200010 VALUES LESS THAN(200010) SUBPARTITIONS 2,
PARTITION TEST_PART_200011 VALUES LESS THAN(200011) SUBPARTITIONS 2,
PARTITION TEST_PART_MAX VALUES LESS THAN(MAXVALUE) SUBPARTITIONS 2
);
CREATE INDEX TEST_PART_IX_001 ON TEST_PART(C_NBR,LINE_ID);


Pls advice
Thanks in advance
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 1 2006
Added on Mar 31 2006
3 comments
1,035 views