I have a database that has several BLOB columns scattered across multiple tables. Since I am a developer and am looking at making application code writing easier, I wanted to turn these columns into NUMBER FK columns referencing a table dedicated to BLOBs only (i.e. a PK ID column and a BLOB column). This would allow our ORM library to do lazy initialization of objects much more easily with less code.

What are the performance or data organization implications to doing this? The database is quite large in size due to these BLOBs.

  • 1
    BLOBs are actually stored this way by Oracle already, ie out-of-line in a separate "table" (called a Segment by Oracle). Commented May 23, 2013 at 20:18
  • 1
    Why would splitting up a table into two tables make "application code writing" any easier. Now you need to first get the PK from the base table and get the blob from the second. Or always join the blob table to the base table. Seems an awful big overhead for a non-existing problem.
    – user1822
    Commented May 23, 2013 at 20:32
  • 1
    Because of Colin's comment, the overhead is non-existent. And the reason the application code is easier to write is because of the Hibernate ORM's mappings. When mapping an entity that you want to be lazy-loaded, you have to create a separate class for the BLOB anyway, but complexity is multiplied when the blob is in the row with your other data (essentially you have to fake the ORM into thinking it's two tables joined by a common key).
    – Andy
    Commented May 28, 2013 at 14:08
  • I suggest to change the title - "STORAGE IN ROW" is a specific clause in Oracle, which impacts internal structures, not related to OP's user-visible table. dba.stackexchange.com/questions/8770/…
    – kubanczyk
    Commented Jun 11, 2013 at 8:51

1 Answer 1


Generally, if you move a BLOB column to another table, and the new BLOB column has the same attributes (such as "DISABLE STORAGE IN ROW", see my other answer), performance wouldn't change much. How Oracle internally stores LOB/BLOB/CLOB columns is, as I understand, not relevant to you, as your question deals more with the user-visible data model.

One obvious performance implication of using a foreign key is that it slows access down a little bit.

But throwing multiple BLOB columns into a single column is a very bad idea in terms of design. Although I don't know Hibernate ORM, to me your question sounds here as "Is it a good idea to spoil the design of database to fix a problem with Hibernate?". Well, it's not a good idea.

What if you need to export or import blobs referenced from only one of your tables?

How would you partition your blobs if they are in a single table? (If your database is "large", you need to plan for that already!)

What if the BLOB updates/inserts that were concurrent previously, now get serialized because you have a single BLOB column?

  • DISABLE STORAGE IN ROW is being used already. However, if blobs moved to their own table, storage in row may be allowed (as PK and blob would be the only two columns at that point anyway). And while I understand your design concern, I don't know that it's necessarily a problem. Blob metadata is stored in the row currently, alongside the blob itself. This move would merely be an abstraction of the data, not really much of a design flaw.
    – Andy
    Commented Jun 11, 2013 at 13:38

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.