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.
Partitioning
Posted - 02/23/2013 : 08:20:56
