Local indexes vs Global indexes for partitioned tables in Oracle

Question

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.

Answer 1

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

Answer 2

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.