Manage Learn to apply best practices and optimize your operations.

How to quickly retrieve data from a large transaction table

I have a very big transaction table. Every day thousands of records gets inserted. I have to maintain only the last five days' data in the production; on 10th day I want to remove the first five days' data and copy them to another location. This continues every five days. I have thought of going for partitions based on record_created_dt. Please give me a better solution.

Also how can I improve the speed if I access the data from the last two to three days? As of now, if I go for partitioning, the last five days' data sits in one particular tablespace. Give me the best solution so that my query retrieves the data very quickly.

You are on the right track with partitioning. From what I gather of your problem, you have all five day's worth of data in one partition. This means that all of the data resides in one and only one partition once you have removed the old data. Have you considered using one partition per day? This way, you will have five partitions for five days of data. When you query only two days worth of data, partition pruning will eliminate three partitions for you. This can save tons of time.

Dig Deeper on Oracle and SQL

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.