Table Partitioning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Table Partitioning

  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Table Partitioning

    Hi All,

    I have a table which might have upto a 100 million records in a year. It has a date column and I need to have 12 partitions for this table, one for each month.

    These partitions need to be such that records having date of January should get inserted into the "JAN" partition irrespective of the year of the date.

    For example: Records with 31-Jan-2001 and 01-Jan-2003 as dates should both get inserted into the JAN partition.
    Similarly for records belonging to Feb and the rest.

    I am working on 9i and I want to avoid adding an extra column to the table corresponding to the month name or number.

    Any pointers on this would be of great help.

    Thanks in advance!

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you can just partition by range on the date column and have a bit of code which truncates that partition just before it get's loaded into the first time

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Table Partitioning

    Originally posted by nottynitin

    These partitions need to be such that records having date of January should get inserted into the "JAN" partition irrespective of the year of the date.

    For example: Records with 31-Jan-2001 and 01-Jan-2003 as dates should both get inserted into the JAN partition.
    This is the part that makes this a little more difficult than having one partition for each month/year.

    I am thinking that the way to do this would be to have a seperate column with either the month name or month number and populate it with a trigger.
    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."

  4. #4
    Join Date
    Mar 2002
    Posts
    534
    Is there any reason why you could/would not create a JAN2003 partition?

    Which is your advantage of having only 1 JAN partition for all years?

    From my point of view it seems that your idear of having one partition per month but not per month/year reduces considerably the advantages of partitioning.
    Last edited by mike9; 11-18-2003 at 01:31 PM.

  5. #5
    Join Date
    Oct 2003
    Location
    Sydney
    Posts
    29
    I am curious what makes you partition by month?

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