Local indexes vs Global indexes for partitioned tables in Oracle

oraclepartitioningperformanceperformance-tuning

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.

Best Answer

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