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

Range or hash for partitions?

I'm in a fix as to which option to choose for partitioning a table -- range or hash. The scenario is like this: I have a table with, on an average, four million rows. There is a unique identifier in the table (ID) that is generated sequentially for every row inserted. There is also a status column -- open or closed. When a row is inserted, a new ID is generated, and data are inserted into the table with 'open' status. Once a user processes the information and chooses to 'close', then the row is deleted and archived in a different table. And the rows are not deleted sequentially. It is at the discretion of the user and depends on which ID they choose for analysis.

  1. Is it worth to go for range partitioning for such a volatile table?
  2. Say I go for range partitioning, then data will not be evenly spread across the partitions. In such a case will the DB Space (blocks) be kept reserved and unused or will Oracle release it? (Well... actually how does Oracle allocate space for a partitioned table?)
  3. Is there any advantage of using hash partitioning in such a case? (Oracle manages the partitioning internally and thus does the overhead of space decrease?)
  4. Would it be a better idea at all to go for index partitioning? Is there any difference/advantage over table partitioning?
Any ideas to bail me out would be highly appreciated.

Frankly, in order to get the "right" way of doing it, you'll simply need to try the various scenarios in a test environment and see which one really works best. What you may actually find out is that good indexes on appropriate columns serve just as well as partitioning. But... let's take a stab at your questions just for fun.

1) I'd not do range partitioning on such a table... it doesn't really make sense as there is no real range of values (like a date for instance).

2) Answered this one in #1... don't do range partitioning.

3) Hash partitioning would be a more reasonable approach.

2 & 3) Space allocation... extents are extents. When space is needed, Oracle will allocate extents based on the table's storage parameters. Once allocated, that space is not released unless you do something (like a table reorg) to recover it. So, even if 10 GB is allocated, and then you delete all the data except for 1 GB, you'd basically have 9 GB of already allocated space ready for receiving new data when it comes along.

4) Creating partitioned indexes is a whole different, but related, ball game. When you create a partitioned table, you may or may not want to create indexes partitioned in the same manner. Personally, I've never looked at just partitioning an index without partitioning it's associated table, so I can't really tell you much on how it works performance-wise.

For More Information

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.