Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
BLOBs are actually stored this way by Oracle already, ie out-of-line in a separate "table" (called a Segment by Oracle). –  Colin 't Hart May 23 '13 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. –  a_horse_with_no_name May 23 '13 at 20:32
    
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 May 28 '13 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 Jun 11 '13 at 8:51

1 Answer 1

up vote 1 down vote accepted

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?

share|improve this answer
    
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 Jun 11 '13 at 13:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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