500 million rows every (8 hour) day

From: Richard Last <richard_ln_at_yahoo.com>
Date: Mon, 23 Nov 2009 18:48:53 -0800 (PST)
Message-ID: <884bd094-e613-47fe-8c4c-f6e9ea2d69dc_at_e7g2000vbi.googlegroups.com>

I've been asked to investigate how to best build an Oracle DB that will be able to handle the insertion of 500 million rows every 8 hour work day. The data will be batch inserted via sql loader every few seconds, into two main tables, distributed about 8:1. Each table is about 30 columns wide. Around 6 months of data will be retained.

At the end of the day, a few stored procedures will have to be run that produce 4 or 5 reports within a few minutes. Each of the reports will contain about a million rows. Afterward, about 50 other reports will be run. All of the reports will run against the data inserted during the day, with the exception of maybe .1% of the data which will reference data from a previous day.

I fully recognise i'm not providing sufficient detail for anyone to provide very accurate recommendations. What I'm curious to know if it's too tall an order for Oracle, or if it will require hadoop, or similar technology. I've done quite a bit of SQL Server work, but not nearly to this scale.

thx Received on Mon Nov 23 2009 - 20:48:53 CST