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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Database Design Web Links: tips, tutorials, scripts, and more.
- Have a Database Design tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Database Design questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Database Design guru is waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Related Q&A from Pat Phelan
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.