splitting the table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: splitting the table

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Posts
    384
    I have a very large table (Partitioned already ) with about 30Gb in size .
    Once a row is inserted Normally we do not update or delete any records in it .
    The daily inserts will be only 100-200MB per day for this table ...


    To back this 200MB i backup a table which is 30Gb and which makes the dumpfile 19GB.
    In case of any imports /recovery it is very time consumming

    How do i split the table into two separate tables (Say old and new).
    I want that to made as a read only tablespace then to be backed only once ...


    I will jion the two tables as a single table as view ..

    How how do enforce the constarints(Primary and unique etc ..)
    It has to check for the values in the old table also ...

    Any help will be of much use ...
    Radhakrishnan.M

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Why not split the partition, put the "old" data in it's own tablespace, make the tablespace that contains the "old" data as readonly.

    This way, you don't have to worry about creating a join, etc.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Sep 2000
    Posts
    384
    already The table is partitioned into 40 diffrent partitions..

    When we insert according to the range value it gets inserted into any off the 40 partioned tables ..
    and all the 40 partioned is in use ...

    If I make any of them read only then it will give errors ...
    Radhakrishnan.M

  4. #4
    Join Date
    Jul 2000
    Posts
    521
    why don't you write atrigger on this table to identify 'today's' data ?
    svk

  5. #5
    Join Date
    Sep 2000
    Posts
    384
    I have partitioned the table on the key column called doc_type
    which has ranges from 1,2,3,4,-40 ..

    How do I split the partitioned table on the above conditions ...
    Hear i cannot split on 2,3,4


    Yes I can write a trigger and get the rows which are inserted recently ..
    But this not allow me to make the table or tablespace as read only which is what i want ..
    Radhakrishnan.M

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The problem with splitting the table into two tables is how do you enforce the PK on both tables.

    First, you need to figure out how to identify the "old" records. Usually, this is done with some sort of DATE field. Once this has been determined, I would use this as my primary partition key.

    Assuming doc_type is of some importance, I would then create a partitioned global index using doc_type as the partition key along with any other attributes you need.

    Lastly, I would create single PK with a regular index. This way, your PK will still be enforced, you can have "old" data in a read-only tablespace, and "active" data in a read-write tablespace. Just remember to keep your PK in a read-write tablespace and rebuild it whenever you split or move partitions.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Sep 2000
    Posts
    384
    Thanks a lot ...
    Radhakrishnan.M

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