Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
Win a copy of Learning OpenStack Networking: Build a solid foundation in virtual networking technologies for OpenStack-based clouds this week in the Cloud/Virtualization forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

BLOBs and CLOBs  RSS feed

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...
author & internet detective
Posts: 38564
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!