Oracle partitioning list or range or ?

szadba used Ask the Experts™
I have a very large table that needs to be partitioned for ease of loading and archiving data. Users require that we can archive data off by an identifier called sample_id  (that is not the table's primary key).  

What would be the best partitioning approach for this example so that each sample has its own partition in the table and when we need to load a new sample, it will automatically create a new partition with its own indexes and constraints. I'm looking for any helpful recommendations. We are running Oracle 11.2.  I was thinking list range or list hash but am not sure how to proceed. If you have any small examples that would be great also.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I would go for range or range/list partition. Here is the example
Most Valuable Expert 2011
Top Expert 2012
use a range partition with an interval of 1

it might look something like this...

CREATE TABLE partition_test
  some_pk_column  number primary key,
  some_date date,
  some_number number,
  some_string varchar2(100),
  sample_id  number not null
INTERVAL( 1) (PARTITION first_partition VALUES LESS THAN (2));
insert into partition_test values (1,sysdate,123,'first test',1);
insert into partition_test values (200,sysdate,123,'second test',2);
insert into partition_test values (333,sysdate,123,'third test',3);
insert into partition_test values (334,sysdate,456,'fourth test',3);
select table_name,partition_name,high_value from user_tab_partitions where table_name = 'PARTITION_TEST';
Most Valuable Expert 2011
Top Expert 2012

note that there are 3 partitions when you are done,  2 with 1 row each and the third with 2 rows (both for sample_id =3)


Thanks. I followed the second example and it worked.  Another question - is it possible for each partition to have it's own set of constraints (foreign keys) and indexes created when the partition is automatically created via interval? If we need to reload the data, it helps us to disable the keys/indexes on the partitions without having to do it for all the data.  I wasn't sure if I have to manually create the keys and indexes after Oracle automatically creates the partition.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial