Suggest me on partitioning

[tatireddis]

Hello All:

I need some suggestions on which one will be better between the two options.

I have one table TEST

1. One Million records insert in a day(avg).
2. It must contains data of the latest 40 days.
3. More queries on the data (res. time .10) sec acceptable.
4. Must be able to retrieve the latest (10000) records (res .02) sec.

There are two options on for me in partitioning.
Pl. consider the maint. problems encountering in the maint. of partitions and suggest me which one will be the better, else if u find a better suggestion give the steps how to implement.

MyOptions.

1. Create a table TEST with 40 partitions part_1,part_2.....part_40. Each are subsequently subpartitioned.
Every day I drop the partition which is not eligible for the 40 day criteria, create a new partition.


2. Create a table TEST with 52 partitions based on the no. of weeks per year, subpartitioning on day(7 subpartitions , no. of days per week).
Truncate the subpartition which is not eligible for the 40 day period.

From the above two,

option(1)
1 Milllion records are partitioned/subpartitioned, query can have a select statment from the subpartition itself.

select from test subpartition <>;

Added to the above it will have to undergo maint. operations,
drop partition and add partition ( to allow the 40 days of data to be available).

Option(2)

1 Million records are in the subpartition

select from test subpartition <>;

which have to query the whole subpartition.

added to this i can say whichever subpartition is not eligible to be truncated.

So now, i have to consider both the options of maint. cost as well as the query cost.

Suggest me which one will be best.

or suggest me to have the other option.

Major limitation is i can't change the code in the application, it must be done from the desiging of the table.
Now my question is how best i can change the DB, to optimize the functionality.


thank you

srini

[marist89]

I don't really understand what you are sub-partitioning on in #1, but here's my opinion anyway.

I would take something similar to #1, but instead of having partition part_1 .. part_40, I would create "rolling" partitions named based on the date. For example, you would have part_20020101 .. part_20020301. Each night, you could drop a partition at the bottom and add a partition at the top to maintain your "40 days".

Keep in mind, however, that since you will be doing an ALTER TABLE, you will invalidate any packages that depend on this particular table.

Also, you will definitely want to use LOCAL indexes for the maintenance.