A customer wants me to copy 100 billion records into an empty Oracle database. Almost all records have about 6 fields, only one of them is a varchar-field, and 99.99% of these varchar fields contain null values. He wants this to be done every day. What kind of Oracle server is needed for that, and are there any special things that I need to remember?
+14
A:
100 billion records per day equates to ~1,157,407 rows per second being imported. Yes - that is a significant problem and this will not be the place to get a real answer - if you have those kind of data quantities and a very large budget for the hardware then talk directly to Oracle to get them engaged on the project.
Andrew
2009-11-26 09:18:42
+1 Right. This project is realy huge.
Christian13467
2009-11-26 10:55:29
After a short discussion, this idea was abandoned and replaced with something much simpler.
Lars D
2009-11-27 06:07:35
Sounds like a good idea! :)
Kris C
2009-11-27 12:13:09
+1
A:
You may be in the realm of an Oracle Database Machine there: http://www.oracle.com/database/exadata.html
David Aldridge
2009-11-26 09:22:37
+5
A:
If this is a real customer requirement that you have, then the 1st thing I'd suggest would be to work with them to understand the business requirements behind this technical requirement, if they have the budget to meet the technical requirement. Then to check if there is a better way of meeting the business requirement than with the technical requirement described above. The reason for this is some of the technical concerns below:
To ask how long this will run and to buy lots of disk space? Is this incremental or overwriting?
Every day for a year, or every day for the next 20 years etc.
Even if you assume each field is only a byte in size, that works out at about 1/2 a terabyte a day. If it is a different set of records every day, then that would add up.
Then look at the import side. This again is a fantastic number of rows per second.
Kris C
2009-11-26 09:22:46
The business requirement is simple: make the data in another database available on Oracle, each day. Optimization using replication is not an option, for other reasons.
Lars D
2009-11-26 10:10:23
ok - so you're looking at a daily copy then (i.e not 100 billion *new* records each day). This is more like a reporting replica in that case?
Kris C
2009-11-26 10:17:19
I guess the business requirement I'm questioning is what they need to do with the data afterwards. Do they need all the data in there? Can it be transformed before insertion to help them achieve their aims? Can it be worked upon it the source DB, etc. Also how much does the data in the source database change each day. Loading an incremental feed daily would be easier than a daily full load of this scale. If the daily full load of this scale is really required, then we're talking a non-trivial requirement that as suggested elsewhere should be discussed with the vendor
Kris C
2009-11-26 13:26:07
+1
A:
100 billion records every day! I doubt that the customer really needs this. Ask him why and you'll probably find an alternative solution.
If it turns out that I'm mistaken and your customer really needs that, I'm curious about the reason!
Bruno Rothgiesser
2009-11-26 09:35:52
I'm not sure that the customer knows the amount of data that is involved, but they are willing to spend money on it, so the question is more like: How expensive would it be.
Lars D
2009-11-26 11:54:19