Personally, I would probably opt for the partitioning option. It keeps the application very simple and staightforward, and simplifies the maintenance.
-----Original Message----- Sent: Monday, February 04, 2002 1:26 PM To: Multiple recipients of list ORACLE-L 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 _________________________________________________________________ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oracle dba INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
