We have a table in which we store millions of small BLOBs (2-10kb). And we need to access a portion of this table at a time, iterating through it while retrieving all these BLOBs (this "portion" is usually in the order of 10**5 rows).
We use oracle 11g R1.
Right now I am thinking about tuning the OracleConnection.setDefaultRowPrefetch()
and give it a hint it's read-only.
What other tuning could be possible?
Also, does somebody have experience with handling small BLOBs with oracle 11g R2? Somebody told me that it is better optimized compared to R1 for handling small BLOBs, but I was wondering whether it's worth the try.
Any advice is appreciated!
EDIT: All rows in the "portion" will be used -- they will be processed into a special big binary file that will be consumed by another entity. So the iteration itself cannot be avoided.
EDIT: Our current DDL (partial, modified)
TABLESPACE "BLOB_STUFF" LOB(
"STUFF"
)
STORE AS BASICFILE "BLOBS_FILE"(
TABLESPACE "BLOBS" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
);