Parameters to tune when retrieving a lot of small BLOBs (2-10kb) from Oracle DB?

Something to consider at the Oracle level: Ensure the LOB column is created (or altered) with the CACHE and ENABLE STORAGE IN ROW clauses -- otherwise every read for every row will require a two direct path reads, which will be slooooow. Smaller LOBs will be stored inline, and larger LOBs will then be stored out-of-line.

Thanks Adam. I posted our current DDL. Guess we are already storing in-line.. – Enno Shioji Mar 10 '10 at 21:58 Both are important: Direct Path Reads require synchronous I/O operations for every row in the data set.

– Adam Musch Mar 13 '10 at 3:58 Thanks for your follow up. Unfortunately something else came up and I can't test your suggestion right now, but I'll try as soon as I have my hands free! – Enno Shioji Mar 15 '10 at 3:18.

We need to access a portion of this table at a time, iterating through it while retrieving all these BLOBs There is a common anti-pattern for OO programmers to write their application so that it just grabs a slack handful of rows from the database and then iterate through them in the middle tier to winnow the rows which are required from the chaff. A more efficient approach is to write a focused query which retrieves just the precise rows. If this is not what you're doing then you should edit your question to clarify your processing.

If it is what you're doing you should explain why you need to iterate through one hundred thousand records at a time.

Thanks for the comment! I edited the question per your advice. – Enno Shioji Mar 10 '10 at 7:05.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.