DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: partitioning an existing table

  1. #1
    Join Date
    Sep 2002
    Posts
    9
    I want to partition an existing non-partitioned table. Can I do this without recreating the table?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Kinda yes, kinda no.

    You have to create another partitioned table with one partition and then exchange your existing table into the partitioned table. When the exchange is done, you can slice it up any way you want.
    Jeff Hunter

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    Yes it should be possible with range partitioning, but it will create new segements for the new partitions and move the related rows in there which is more or less the same as recreating a new partitioned table and then insert there all data.

    To do this you would have to:

    1) create a new and empty partitioned table (called "new_part_table") with only one partition (called "part1") with the upper limit VALUES LESS THAN (MAXVALUE)

    2) exchange your partiton with you actual table
    ALTER TABLE new_part_table
    EXCHANGE PARTITION part1 WITH TABLE your_old_table;

    3) split your partition
    ALTER TABLE new_part_table
    SPLIT PARTITION part1 AT ( 500 )
    INTO ( PARTITION part1, PARTITION part2);

    4) repeat step 3 for additional new partitions

    5) you will probably have to rebuild the indexes


    I have not tested it but it should work, however I guess that it would be faster to simply create a new partionned table, insert all data, drop your old table and rename your new table to the old table name.

    HTH
    Mike



  4. #4
    Join Date
    Sep 2002
    Posts
    9
    Thank you all!

    Mike's method works. I think it's a usefull alternative of the good old export, re-create table, import.

    CfiDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width