Problem solve Get help with specific problems with your technologies, process and projects.

Table partition for insert performance?

We have a table that is growing at 2 million records per day. Most of the online queries hit on this table. We have about 5 indexes on this table. As days go by, insert is taking more time on this table. My question is, should we go with table partition for better insert performance and not lose the query performance? We do have a date column on this table and would like to know whether we can consider this column for range by partition.
The reason that your insert is taking longer is that the five indexes have to be updated before the insert completes. Partitioning can greatly help in this case because instead of the insert happening in the entire table, only a small portion is considered. And if you use local indexes on that table, then only a small portion of the index, corresponding to that partition, will be affected as well. With 2 million new rows a day, this table can be an ideal candidate for partitioning.

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.