Regarding partitions [message #51789] |
Thu, 13 June 2002 11:36 |
Rahul
Messages: 94 Registered: December 1998
|
Member |
|
|
Hi all,
I got a question..can some body let me know how can we partition a table which has all the data? I mean there is a huge table and I want to partition it now...how can I do that?
Regards,
Rahul
|
|
|
Re: Regarding partitions [message #51792 is a reply to message #51789] |
Thu, 13 June 2002 11:55 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
Oracle has already defined these cases
<u>method 1) Export/import method</u>
1) Export your table:
2) Drop the table ( or better rename the name...to
be in the safer side)
3) Recreate the new table with partitions:
4) Import the table with ignore=y:
The ignore=y causes the import to skip the
table creation and continues to load all rows.
<u>method 2). Insert with a subquery method</u>
1) Create a partitioned table:
2) Insert into the partitioned table with a
subquery from the non-partitioned table:
3) If you want the partitioned table to have the
same name as the original table, then drop
the original table and rename the new table:
<u>method 3). Partition Exchange method</u>
Make use of ALTER TABLE EXCHANGE PARTITION
1) Create table dummy_t as select with the required partitions
2) Alter table EXCHANGE partition <partition_name>
with <non_partition_table_name>;
<u>example:</u>
SQL> CREATE TABLE p_emp
2 (sal NUMBER(7,2))
3 PARTITION BY RANGE(sal)
4 (partition emp_p1 VALUES LESS THAN (2000),
5 partition emp_p2 VALUES LESS THAN (4000));
Table created.
SQL> CREATE TABLE dummy_y as SELECT sal
FROM emp WHERE sal<2000;
Table created.
SQL> CREATE TABLE dummy_z as SELECT sal FROM emp WHERE sal
BETWEEN 2000 AND 3999;
Table created.
SQL> alter table p_emp exchange partition emp_p1
with table dummy_y;
Table altered.
SQL> alter table p_emp exchange partition emp_p2
with table dummy_z;
Table altered.
|
|
|