- Is it worth to go for range partitioning for such a volatile table?
- 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?)
- 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?)
- Would it be a better idea at all to go for index partitioning? Is there any difference/advantage over table partitioning?
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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our applications, PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.
This was first published in August 2003