Oracle Blobs - store size or calculate?

Question

I am writing a generic Http resource hosting service and am storing larger objects as BLOBs in an Oracle database. I want to be able to set the 'Content-Length' header when returning a stored object, which means I need to know the size of the BLOB before I start writing it to the client (I know I could use chunked-encoding, and am in some cases). Does anyone have any experience with the performance impact calling dbms_lob.getlength() will have on every read or should I calculate the BLOB size on INSERT and store it in the table? On average I'd expect write rates to be higher then read rates. I'm writing a benchmark right now to try and see what the impact is, but it seems like such a common question I thought someone might have already figured this out. Also, using JDBC/Spring 3, how would I even calculate the BLOB size on write? (and I can't use triggers or stored procedures) Thanks.

Answer 1

I did a quick check selecting a BLOB from a table and then a LENGTH(BLOB) and DBMS_LOB.GETLENGTH(BLOB). When selecting the BLOB itself, I got 44 consistent gets. When I selected the length (by either method) I got 7 consistent gets.

Based on that, when I get the length, it does not retrieve the entire blob and calculate the length. It is sensible to assume that the length it stored at the start of the BLOB (like they length of a VARCHAR2 value is stored) and this is used directly.

As such, there shouldn't be a great overhead in deriving the length rather than storing it. It also reduces the chance of an inconsistency.

Answer 2

Because our BLOBS compress well in general, we have taken this approach:-

• store the BLOB compressed. The compression is done on the java side as we stream into the BLOB
• record the uncompressed size in bytes in another column in the same table
• uncompress via a stream as we send the BLOB out again, knowing what the content size will be

You could consider this approach if your BLOBs are compressable.