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,
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)
store in ( ts1, ts2,ts3)
Hope this helps
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.
[Edited by anandkl on 03-18-2002 at 08:34 AM]
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.
Click Here to Expand Forum to Full Width