Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Partitioning and maintenance

    So, I've just been getting my feet wet in the documentation, and I already have a question:

    We have a table with YEAR and QTR separated into their own columns. I'd like to use these as a basis for partitioning, but it looks like there'll be a maintenance overhead of going in, perhaps annually, and altering the table to include 4 new partitions for every year?

    Am I on the right track? It'd be much easier if there was an automated way of declaring that a partition be created everytime a unique set of values were introduced into the system. That's what I'm hoping for, I guess.

    v. 10.1.0.4
    ---=Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    manual maintenance is required & if/when you forget it is a real pain to clean up the mess.

    You need to read up on HIGH VALUES.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Is is still a pain if you have ENABLE ROW MOVEMENT in place, alongside a MAXVALUE partition? I was hoping that in the case I forgot to create a partition, and rows were inserted into the MAXVALUE partition, that an ALTER TABLE which added a new partition would automatically move the appropriate MAXVALUE rows.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Think about the following restrictions.
    1) Partitions can only be added at the "top".
    2) A new partition can not be added "between" two existing partition.
    3) No value is greater than HIGH VALUES; which means a partition can not be created on "top" (after) the HIGH VALUES partition.

    IIRC, Oracle won't allow a partition to be dropped if it contains data.

    So when you forget here is what needs to occur.
    1) Move all the data out of the HIGH VALUES partition.
    2) Drop HIGH VALUES partition
    3) create new partitions
    4) create HIGH VALUES partition
    5) move data into appropriate new partition

    All of which can be disruptive for 24x7 application
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I don't know what version you are using, but Oracle 11G now has automatic partition generation. Maybe you should look at upgrading if you want this option.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Alternatively why not just create your future partitions now if you have the disk space. We tend to create our partitions going upto 5 years in advance.

    Alternatively the partition maintenance isnt really that big a job unless you have loads of partitioned tables in which case you could create a procedure to add the new partitions and call it from an oracle job near the end of the year.

    Alan

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    That's a good point. We're not really poised to endure the testing necessary to go to 11g at this time.

    It still sounds like a yearly job, in itself, wouldn't be sufficient if I have a category which includes the MAXVALUE keyword, though. At least things wouldn't be easy. I'd have to add some code which moved all data out of that top partition, dropped that partition, added some new partitions at the top (including a new MAXVALUE partition), and then moved the data back into the table.

    Does that sound like I'm on the right track?
    --=cf

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you are proactive, then the HIGH VALUES partition will never contain any data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    OK, I see what you're saying. So in that case, dropping the HIGH VALUES partition, adding new partitions at the top, then re-adding the HIGH VALUES partition, requires 0 data movement.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by chuck_forbes
    OK, I see what you're saying. So in that case, dropping the HIGH VALUES partition, adding new partitions at the top, then re-adding the HIGH VALUES partition, requires 0 data movement.
    That is 100% correct; assuming it is done PROACTIVELY

    This operation can be automated to run as a batch job once a year;
    or it can be done manually during normal working hours.
    Nothing in the application will be referencing HIGH VALUES or the new partition(s).
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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