Problem solve Get help with specific problems with your technologies, process and projects.

Overview of partitioning options

We are trying table partitioning for the first time ever and have a gamut of options opened up by Oracle9i. The choice lies between range, hash, list and composite partitioning as well as index-organized tables.

The table will have a about four to five million records and has a unique 'Identifier' field to base the partition on. The identifier field is not the primary key but a not-null field designated explicitly for the partition identifier value (a number). This identifer actually represents three criteria: whether the record is 'active' or not, 'type' of record and 'application' it belongs to. 1) Which option of partitioning would be ideal to go for? 2) What is the maximum number of partitions per table that should be created? (In our case we need 3 paritions per table to accomodate our requirement) 3) Is it possible to allocate different paramters like space to each partition? In other words is it necessary to have all partitions of same size? 4) Would it be appropriate to replace the single 'identifier' field to be replaced by a composite partition based on the three criteria listed above?
Range partitions require that each partition boundary compare greater than or equal to the partition bound for the previous partition while list partitioning allows partitioning by any list of values. Since it sounds like the value is constant and not a range of values, then I'd likely use list partitioning.

For example:

create table my_table (col1  number,  partition_identifier number, ......)
  partition by list (partition_identifier)
  partition active_rec_app1 values (101,111,121,131),
  partition inactive_rec_app1 values (201,211,221,231);

The maximum number of partitions (Oracle's theoretical limit) is 64K-1. In reality, when you get near 10K partitions, you start to see some problems, but until you get that number of partitions, I wouldn't worry! Three partitions per table is small potatoes!

You can create a different tablespace for each partition so therefore, you can have different storage parameters for each.

If the single identifier field you are using is cleanly bounded, replacing it with three different values wouldn't really provide any benefit other than to be "better documented."

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.