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

Thread: Partitioning ...

  1. #1
    Join Date
    Jan 2002
    Posts
    78
    Hi All,

    I have a table in which we are storing date column( Column name is process_date) as varchar2(6) in 'YYMMDD' Format. As to improve our database performance i am looking to implement partition concept.

    At any point of time we need to keep four months of data in our database. Suppose this is Mar month we are having Dec,jan,Feb and Mar months data ( basing on process_Date).

    Now i am looking to implement partitions.

    I need the Partitions like...

    P1 contains 1st, 4th, 7th, 10 th Months data

    p2 Contains 2nd, 5th, 8th, 11 ths Months data

    p3 Contains 3rd, 6th, 9th, 12 ths Months Data.

    Please help me how to implement these partition concepts.

    Thankx and Regards,
    Sekhar

  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    First, be aware of the fact that we cannot partition on a calculated value. We only can partition using a table column.

    For your problem, I see 2 options.
    You use range partitioning , or you use list partitioning ( only 9i )

    1. range partitioning

    You define an extra field in your table where you will put a number that indicates the partition where your rows has to be stored, according to the value of process_date.

    A value off 020101 in process_date will return 1
    A value off 020201 in process_date will return 2
    A value off 020301 in process_date will return 3
    A value off 020401 in process_date will return 1
    ...

    You apply range partitioning
    create table ..
    partition by range (MyCalculatedFieldInMytable )
    (
    partition p14710 values less than (2) -- stores 0 and 1
    partition p25811 values less than (3) -- stores 2
    partition p36912 values less then (4) -- stores 3
    )

    2. List partitioning

    You apply the same method, but now you can calculate the month-value off the process_date field.

    You can use list-partitioning

    create table ..
    partition by list (MonthCalculatedFromProcess_dateField )
    (
    partition p14710 values (1,4,7,10),
    partition p25811 values (2,5,8,11),
    partition p36912 values (3,6,9,12)
    )

    If however, you only want to spread you data accros different partitions, it may be better to use hash-partitioning on the process_date field.

    create table ...
    partition by hash(process_date)
    partitions 3
    store in ( ts1, ts2,ts3)


    Hope this helps
    Regards
    Gert

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Why do u want to place your data in this manner.Do you see any performance gain.You can place the first 4 months in first partition similarly next 4 in the other partition this way you can create 4 partitions.I guess you will be getting the same benifit as the other.More over i guess you cannot create partition for ur requirement as you would be specifying more than one boundry for your partition limit and this is not allowed in oracle.This my understanding for ur requirement.


    cheese
    anandkl

    [Edited by anandkl on 03-18-2002 at 08:34 AM]
    anandkl

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Why not just put one month's worth of data in it's own partition? You can spread those partitions out however you want on disk.
    Jeff Hunter

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