• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

BLOBs and CLOBs

 
Stephen Huey
Ranch Hand
Posts: 618
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We're converting to Oracle (10g specifically), and now we're going to start storing in the database a bunch of files that our web app used to store in flat files outside of any database (including XML, HTML, other text files, and some kinds of images). All of these files would be associated with a record in our Transaction table (since there would be a one-to-one correspondence between a TransactionID and each of the two possible kinds of images and an HTML file), but I was wondering whether or not it would be better to store these bigger items in their own table(s) for performance. It is necessary to retrieve these files only some of the times that you would want to access any of the 30 fields already in a Transaction record. I was even thinking about having all the CLOBS associated with a TransactionID in one extra table and all the BLOBS in yet another one. Would this possibly be worth it (or not too bad), or should we just tack these fields onto our current Transaction table?

With Oracle JDBC (maybe any JDBC, I guess), if you're not ready to insert a LOB field at the time you're inserting the rest of the record, then you have to use an EMPTYBLOB placeholder. So we're already going to be hitting the table with extra UPDATEs to tack on these LOBs once they're generated...don't normal forms get sacrificed a bit pretty often for the sake of performance?

Thanks for the advice...
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Stephen,
I agree it is better to store the BLOBs/CLOBs outside the main transaction table. The performance improvement comes from having more "real transaction" records on the same I/O page. For large queries, there could be a significant time difference.

This isn't bad when it comes to normal form. There is still a primary key and a logical arrangement. The problems come when people stick duplicate data in a database in the name of performance.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic