Peter Ocelka, August 07, 2012 - 1:59 pm UTC
Hi Tom,
I'm sorry for the later answer, I was kind of busy. Thanks for explaining that interval feature is possible only for range partitioning.
The thing what I wanted to do is:
I have two processes which are running in parallel; with IDs 1999 and 2000. Each process will process i.e. 20 000 records. We are migrating data into the Siebel where for better performance you split the data into additional batches, each batch contains let's say 2000 records. Since the processes are running in parallel you have to generate this batch range dynamicaly. I use this format: to_number(v_prefix || lpad(p_drop_id,6,'0') || v_sufix) /11001999000 - 1100199010 and 11002000000 - 11002000010/. So I wanted Oracle to generate automatically for each Siebel batch number one partition (because I was too lazy to create procedure which will add partition, which needs to be called over DB link etc.), at the end for each process I would have 11 partitions. If you create interval range partitioning than if process 2000 is faster than 1999 (i.e. different amount of the records to be processed) then Oracle creates partition for process 2000 as "with values less than 2000". If you want to drop the partition somewhere in the processing you are risking that your are dropping the data also for the process 1999 which finished before you execute the partition drop 2000.
Anyway we created now the procedure which will add partitions to list partitioned table, so the whole batch range 000 - 010 will be in one partition.
You may missing something
Mike, August 15, 2012 - 9:11 am UTC
You said:
If you create interval range partitioning than if process 2000 is faster than 1999 (i.e. different amount of the records to be processed) then Oracle creates partition for process 2000 as "with values less than 2000". If you want to drop the partition somewhere in the processing you are risking that your are dropping the data also for the process 1999 which finished before you execute the partition drop 2000.
If you create the table as ranged partitioned with interval 1, then 1999 and 2000 will be in separate partitions. How would dropping one of these impact data in another?
Data datatype
Swapnil Patil, April 02, 2019 - 6:39 am UTC
Hi Tom,
Can we do the same with date as datatype?
April 02, 2019 - 8:16 am UTC
Yes, dates work just fine.