Partition

[rumcool]

Hi all ,
I have a requirement that i have crores of datas to be processed in a year,
For performance sake,
I have created range partition on a table depending on dates,
like
PARTITION BY RANGE (sys_dt)
(PARTITION part1 VALUES LESS THAN (TO_DATE('01/03/2003', 'DD/MM/YYYY')),
PARTITION part2 VALUES LESS THAN (TO_DATE('01/03/2004', 'DD/MM/YYYY')),
PARTITION part3 VALUES LESS THAN (TO_DATE('01/03/2005', 'DD/MM/YYYY')),
PARTITION part4 VALUES LESS THAN (TO_DATE('01/03/2006', 'DD/MM/YYYY')),......
PARTITION part5 VALUES LESS THAN (MAXVALUE)
)
suppose i want 1 year of data like from this july , then according to the partition i created , its going to access part2 and part3 , again its going to be performance issue , is there any other method where i can avoid it or is this the only solution .


thanks in advance

[mike9]

If you want data of july 2005 if will only access pt4.

Pt3 contains only data before 2005/03 which means that this partitions will contain no data related to 2005/07. Pt5 will only contain data >= 2006/03 so it neither contains any data related to 2005/07. The only partition containing data related to 2005/07 will be Pt4.

If you want the data from 2005/07-2006/07 it will access pt4 and pt5. If you want the data from 2004/07-2005/07 it will access pt3 and pt4.

If you often only access data of a specific month it maybe worth to partition your table on a monthly base.

HTH
Mike