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.
- 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.
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.