0

What strategies are available to import about 500 million small transactions every day from an external system?

Oracle becomes very slow with very frequent commits.

The transfer process which is reading the data from the external system can be implemented individually.

A NoSQL based solution is possible.

Update: The datasource is a 'transaction journal' of a different DB system.

2 Answers 2

1

You didn't mention how the data comes in, but I would hazard a guess that it is through flat files. If this is the case, I worked at an order processing company that used Oracle for the back-end of the WMS we used. We regularly received 10 of millions of records a day. We used SQL loader scripts to load the data. The caveat to that is that the data needs to be in the proper format. See here:

http://knoworacle.blogspot.com/2012/05/oracle-sql-loader-with-example.html

And of course from the makers:

http://docs.oracle.com/cd/B10501_01/text.920/a96518/aload.htm

0

I've written code that uses a combination of external tables and SQL*Loader using direct mode that over a period of 4 hours would intermittently receive many transaction files. In that time we would load around 100 million records but the script was idle for probably over half that time waiting for data. SQL*Loader in direct mode is VERY fast.

However, it all depends on how the files arrive, what format they are in, the structure you are loading into, the OS (to a degree). Don't forget that if you bulk load you just don't do anywhere near as many commits.

As for the NoSQL point - it always comes down to what is the data, how will it be used and how and when does it need to be consistent.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.