We are using binary large object (BLOB) object to store the document in database.
I want to ask whether document storing in database will hamper the performance of DB and is it ideal practice to store documents in DB?
Storing documents in a database is frequently used and is not something to avoid. However, what you need to do is to understand how the documents will be stored (via an application, via bulk periodic loads, etc) and how they will be retrieved/used.
The contents of a BLOB datatyped column can either be stored in-line with the other column data or out of line in a separate location depending, in part, on your definition of the object and the size of the BLOB. If it is stored in-line, the row data will take up more space in the block and thus fewer rows will fit into a single block. That means there will be more blocks of data that will have to be retrieved to get the row data you want when you only want columns that are not large object types.
But that's not necessarily a problem. Only testing your application usage of these columns will provide you with the detail you need to determine if the BLOB column has any adverse effect on performance. In general terms though, it's not the BLOB datatype that would be the problem. It's the way you store it, access it and use it that will need to be evaluated to verify that all your performance needs are met.
Dig Deeper on Oracle and SQL
Related Q&A from Karen Morton
Learn how to do simple SQL arithmetic operations without using the arithmetic operators. Continue Reading
One reader asks how to create and retrieve a binary large object (BLOB), and SQL expert Karen Morton gives some comprehensive advice and tips. Continue Reading
Expert Karen Morton explains how to use a simple analytic rank function to sort through a group of golf scores. Continue Reading