Usually partitioning is not used as part of purging. If you are planning to purge some of the data, there is really no need to partition that - that data will be gone anyway after you purge. Can you explain in a bit more detail what your goal is?
Thanks James, The task is to Archive of old records for Eg:- My audit transaction report will be executed month wise today if i run i will get set of data of this month only if i run on march 2 i will get data only for 2 days so my partition is month wise every year end my data will be moved to warehouse table by insert and truncate to current live table recently i came across switch partition so i thought of doing switch partition to staging table and moving staging data to warehouse table every month instead of holding a year data. can anyone tell me whether this approach is good to proceed.
visakh, Iam not worried about fetching data because my application will get data only for a current month. currenly my purging rule for my live table is for a year eg:for cuurent year my purging activity will happen on some day on dec 2013 till that time my data will be available in the table as it is a old system we are plaaning for some changes we have concluded this purging activity is taking very long time to process because it is moving data for a year so we thought of moving data every month so we can break some work. my approach is every previous month end i will do switch partition(takes seconds to proceed but doing schema level lock) to a staging table and again i will move it to warehouse table. Note: my table is monthwise partition.so this approach will not reduce IO for application (my fetch will be current month wise) but it saves time for moving data to warehouse table.