Skip to Main Content
  • Questions
  • Interval partition on index organized table

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Brijesh.

Asked: April 11, 2018 - 12:30 pm UTC

Last updated: April 11, 2018 - 4:19 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi,

Some points related to my query,

1. We are going to do partition for table, have almost 1.7 TB in size and have 6 Indexes.
2. We are planing to Use Exchange Partition method.
3. In that table have "Operation_date" column which we want to use for Partition.
4. This is OLTP environment and two year data we are keeping in table and each month we are doing archiving for one month data.
5. Once we define Partition with existing data, need whenever new data get inserted then partition should attach itself, for that we are looking Interval Partiton.

Now the question is, Interval partition we cannot use in Index Organized Table.

Please can you suggest.

Thanks,
Br



and Chris said...

I don't see a question here? Just a statement. Which is correct.

You still (as of 18c) can't create an interval partitioned index-organized table:

create table t (
  dt date not null primary key
) organization index 
  partition by range (dt) interval (interval '1' month ) (
  partition p0 values less than (date'2018-01-01')
);

ORA-25198: partitioning method is not supported for index-organized table

Rating

  (1 rating)

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

Comments

Brijesh, April 11, 2018 - 4:10 pm UTC

Hi Chris,

Thanks for your time.

My question was based on above points, how we can do the partition day wise even every month we are archiving one month data because one month new data will be available to insert.

Thanks,
Brijesh
Chris Saxon
April 11, 2018 - 4:19 pm UTC

I'm still not sure what you're asking.

If you want to load data into a partitioned IOT, then you can create a maxvalue partition. And have a process to split this as needed:

create table t (
  dt date not null primary key
) organization index 
  partition by range (dt) (
  partition p0 values less than (date'2018-01-01'),
  partition pmax values less than (maxvalue)
);

insert into t values (date'2017-12-31');
insert into t values (date'2018-01-01');

commit;

select * from t partition (pmax);

DT                     
01-JAN-2018 00:00:00   

alter table t split partition pmax at (date'2018-02-01') into 
  ( partition p2, partition pmax );

select * from t partition (p2);

DT                     
01-JAN-2018 00:00:00   

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.