Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Peter.

Asked: July 30, 2012 - 5:51 pm UTC

Last updated: April 02, 2019 - 8:16 am UTC

Version: 11.2.0.1.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I would like to ask you about interval-list partitions. On oracle-base.com is mentioned that one of the new features of 11g is interval partitioning (available over number and date columns) and it should be possible to create interval-list partitioned table. I was trying to search the web/documentation for the examples for interval/list partitions and I came with nothing. Every example for interval partitioning is partition by range, which doesn't suite our needs.

Maybe it is a stupid question, but could you please give me an example of interval-list syntax?

I was trying something like this on my test table, however I'm getting an error:
drop table tmp_part_test purge;

create table tmp_part_test
( id_num number,
txt varchar2(4000 char)
)
PARTITION BY list (id_num)
interval (1)
(PARTITION p_0 values (0))
;

Is this even possible?

I'm looking for some inteligent partitioning. I will have a range of numbers i.e. 10002000000 - 10002000099, and I would like Oracle to automatically manage the partitions. This number is generated as:
10 - prefix of the operation
00 - reserved
2000 - generated from the sequence
000 - 099 - range of the batches

I could use the interval-range partitioning however since my processes are running in parallel it is possible that in ID 1999 will be more data than in 2000 (meaning 2000 is faster than 1999) then when oracle first creates partitions for batches containing number 2000 and afterwards are inserted data for ID 1999, these data will be part of the ID 2000 partitions.

After data are inserted to the table, another process runs over this table and process the data. This process is a black-box and it creates conditions like ... and if_row_batch_num between 10002000000 and 10002000099 (I don't have a direct control over this process). After it is finished I would like to drop partitions from this source table.

and Tom said...

interval partitioning *is* range partitioning where the range partitions are added automagically (that is why it only works with numbers and strings)

interval-list partitiong sounds to me like a composite partitioning scheme - but I see you want to do an interval partition based on a list.


You did not tell me how you wanted to partition exactly. Is it that you want to take the "generated from the sequence" bit and partition by that?

ops$tkyte%ORA11GR2> create table t
  2  ( id_num   number,
  3    data     varchar2(30),
  4    seqno     as (to_number(substr( to_char( id_num, 'fm00000000000'), 5, 4 )))
  5  )
  6  partition by range(seqno)
  7  interval (1)
  8  (partition empty values less than (0))
  9  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select partition_name, tablespace_name, high_value
  2    from user_tab_partitions
  3   where table_name = 'T'
  4   order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- ------------------------------------------------------------------------------------------
EMPTY      USERS      0

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t (id_num,data) values ( 10991234666, 'hello world' );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select partition_name, tablespace_name, high_value
  2    from user_tab_partitions
  3   where table_name = 'T'
  4   order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- ------------------------------------------------------------------------------------------
EMPTY      USERS      0
SYS_P1703  USERS      1235

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t (id_num,data) values ( 10997890666, 'hello world' );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select partition_name, tablespace_name, high_value
  2    from user_tab_partitions
  3   where table_name = 'T'
  4   order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- ------------------------------------------------------------------------------------------
EMPTY      USERS      0
SYS_P1703  USERS      1235
SYS_P1704  USERS      7891

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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?

Connor McDonald
April 02, 2019 - 8:16 am UTC

Yes, dates work just fine.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.