| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> need opinion on design
Hi fellow DBAs,
I trying to figure out the best way to do this and would like to hear what you think of it.
I took over this database which has a ACTIVITY table in it. This is a very large table which get's million records per day. The records coming from another server. And we also need to keep like 2 months worth of the records in it for billing purposes.
So, because of the two very different types of activities on the table. I am splitting it into two databases. One is the LIVE database which I will only keep like 1 day worth of data and tune it for handling high volumn of transaction. Then I am building a DW which will store up to 2 months of activities for billing purposes and provisioning, and tune that for big queries. So far so good. Right?
Now, there is a requirement that the provision application which will use the DW needs to see (some times) up to minute data for the ACTIVITY table. So here is where I am trying to figure out the best way to do this.
o I don't want the application to hit the LIVE database for getting
up to minute data, fearing that it will impact the performance on the
LIVE side.
o That means a daily load into the DW is not good enough.
I will need to replicate the data into DW more frequently, like at
minutes interval.
So here is my main question: If I do that, should I in the DW split the ACTIVITY table into two like the ACTIVITY and ACTIVITY_HISTORY or just keep one ACTIVITY table? Now one thing, regardless whether I split the table or not. The large table has to be partitioned.
The reason I am thinking about splitting the table is so most of the long running queries will be running against the A_HISTORY table. And only when the up to minute data is needed, then the app will use the ACTIVITY table in the DW. Or maybe this is not necessary since if I partition the table, then the replicated data coming from the LIVE database will go to one partition while most of the queries will be hitting other partitions?
Please let me know what do you think if this. Any feedbacks are welcome.
Rich
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: oracle dba
INET: oracle12i_at_hotmail.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Feb 04 2002 - 12:30:12 CST
 |
 |