Alter nonparttioned table into partitioned one

Printable View

03-24-2006, 09:07 AM
kholger

Alter nonparttioned table into partitioned one

Hi,
I am looking for the best way, to alter an existing large table to a partitioned (probably hash) one. Create a new partioned one, copy all the data, relink foreign keys, drop the old table and rename the new sounds a bit awful. Is there a better way?

Thaks
03-24-2006, 09:40 AM
marist89

1. create your partitioned table empty with only one partition as new_table.
2. exchange table target_table with new_table
3. rename target_table to table_old
4. rename new_table to target_table
03-24-2006, 09:54 AM
kholger

1. create your partitioned table empty with only one partition as new_table.

OK

2. exchange table target_table with new_table
What do you mean with exchange? Does this also adapt the foreign keys on the target_table to new_table?

3. rename target_table to table_old
OK

4. rename new_table to target_table
OK

Thanks
03-24-2006, 10:05 AM
marist89

Quote:

Originally Posted by kholger

2. exchange table target_table with new_table
What do you mean with exchange? Does this also adapt the foreign keys on the target_table to new_table?

http://download-west.oracle.com/docs...titi.htm#11985

and I forgot:
5. split your one partition table to the partitions you really want.
03-24-2006, 11:38 AM
slimdave

Quote:

Originally Posted by marist89

http://download-west.oracle.com/docs...titi.htm#11985

and I forgot:
5. split your one partition table to the partitions you really want.

Can't split, drop or merge hash partitions, though. Actualy it would be pretty ineffcient to keep splitting, and probably better to create the new table with the required partitions and insert into it.

kholger, when you say "probably hash", it makes me wonder whether you have got the right reasons worked out for using partitioning ... what problem are you trying to solve?