I have partitioned a table that is growing almost at a rate of 7-8 million rows a day. The partitioning has been done using a timestamp column as data can be archived or discarded a few weeks later. I have also created an index on the table which are on primary key or another value that is unique. My indexes are partitioned as well, however, the partitioning of index has been done using a hash function and does not include the partition key of table (which is a timestamp). So I have a few questions.

  • For an ever-growing table which has majority inserts, does partitioning alleviates slowness with inserts and updates or partitioning indexes is enough to gurantee a faster insert and update. I have been told that there may not be value in partitioning indexes and table differently. Does it make sense to partition tables to speed up inserts?

  • The table is a write-intensive table. It is currently mostly inserts, one update per row and 2-3 lookups within seconds of creation by indexed id and then the record is never accessed for any operation.

  • Is it optimal to define local indexes on the unique id or is it better to define global indexes and partition them as I have already done? If I define global indexes, without table partitioning key in it (timestamp, which is not used in lookup), will the access be more expensive if the number of partitions is huge?

  • What are the downsides of having global partitioned indexes for constantly growing data? Once I decide to remove the partitions at a later point of time, since the indexes are not partitioned by timestamp but instead by unique id, will the operation cause a direct impact on functioning indexes?

Any other recommendations will be helpful.

  • "…if the number of partitions is huge…" choose your partition range to match your discard/archive operation. – Jack Douglas Feb 5 at 9:36

…does partitioning alleviates slowness with inserts and updates or partitioning indexes is enough to guarantee a faster insert and update…Does it make sense to partition tables to speed up inserts?

No, absolutely not. The primary benefit of partitioning is the low cost of dropping partitions. You said "data can be archived or discarded a few weeks later", which might make you a genuine candidate for partitioning.

Is it optimal to define local indexes on the unique id or is it better to define global indexes and partition them as I have already done?…What are the downsides of having global partitioned indexes for constantly growing data? Once I decide to remove the partitions at a later point of time, since the indexes are not partitioned by timestamp but instead by unique id, will the operation cause a direct impact on functioning indexes?

Using global indexes goes against the grain of partitioning in the first place — they are marked 'unusable' if you drop a partition.

You may find this ORACLE-BASE article a useful read.

  • Thank you for the article. Unfortunately, the article ponders on the mechanics of partitioning/indexing and not rationale. I understand why you would say that global indexes are against the philosophy of partitioning. But it cannot be discounted that the need I have is realistic. I have constantly growing partitions at a rapid rate. (1 partition every 4 hours with about 2 million rows). I need to be able to do updates for a short time window reliably using a unique key and not timestamp. Timestamp partitioning key is merely for archival need, which is genuine. So what do you recommend? – Priyank Feb 5 at 13:57

Why are you doing hash partitioning and not range partitioning based on date. If you hash partition, you get not benefit of being able to drop whole partitions. What you probably get is that inserts have to potential to be done across a number of partitions and possibly separate storage. You could partition by day and sub partition by hour, thus you write to one sub partition at a time and move to the next. Because you can do interval range partitioning you don't need to keep creating new partitions, just drop partitions as they are no longer needed. Make sure that you have a primary key. Otherwise your indexes will need to be rebuilt every time partitions are dropped. The primary key should be used to filter out all of the partitions that don't contain the data that you want for most queries. At lease when you are querying by date. Local indexes will help within individual partitions or sub partitions once the partition pruning has happened. Global indexes can help with queries that in your case aren't date specific, but need to be selected across all partitions.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.