Manage Learn to apply best practices and optimize your operations.

How to keep data in an Oracle8i database before sending it to an external historical database?

Hi Pat. I am developing an Oracle 8i database in which very high performance is mission critical. The input data are received in an "on-line" way through a message queue and are processed at once. The problem is: I have to keep the received data (already processed) for three-days in the database before they are sent to an external historical database. Can you suggest to me, please, the best techniques (and maybe some useful links...) used for this purpose? Should I use different tables for data to be processed and that already processed, different partitions within the same table, or a column flag? Thank you in advance.

This is a question that I can't really answer. Too much depends on parts of the problem that you haven't addressed. I'd suggest using a single table to store the incoming and processed data, with a column to show the date a row was processed. When the row first comes into the database, the "date processed" column would be NULL. After you process the row, set the "date processed" column to show when you processed the row. After the "date processed" reaches whatever age you define to be expendable, move the row out to the historical database.

Keep in mind that there are many factors that can influence the choice of how to store this data. You might decide you need to use one of the other organizations because of those outside factors.

For More Information

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.