Saturday 16 April 2011

BLOB Vs BFILE

Here are the some extract from Ask tom....

We are working on an application that users will be uploading about 250 10MB per day into our
database. We are reading the first few lines of the file when it is upload, but other than that we
don't use them. All we are doing is storing these files and distributing them to other users. Then
after 45 days the files are archived. We had originally deicded to use BLOBs but we got some
advice to use BFILEs. Here is that advice making the case for BLOBs and BFILEs acorrding to the
circumstances:


> It is always better (if you can) to use BLOBs because of
> following reasons:
>
> 1. Faster access for smaller files (few hundred KBs).
>
> 2. Database security. For BFILEs, OS admin has to maintain
> the security of files. BFILEs are stored outside oracle
> database and it becomes OS admin's responsibility to make
> sure that the files are not overwritten or modifed by someone else.
> For BLOBs, this is not a problem as files are bound by
> database user access security.
>
> 3. Backup and Recovery is easy in case of BLOBs. BFILEs are not
> automatically backed up when you backup the database. It is
> an extra step of backup and admins have to make sure that
> location (pointer) integrity is maintained while restoring
> such an application with BFILEs.
>
> 4. There are more features which can be used in case of BLOBs.
> You can easliy modify the content of file in BLOB. I do not
> think this applies to you - if you are looking for store and
> retrieve kind of application.
>
> So, when do we use BFILEs?? - if you have following considerations:
>
> 1. BFILEs keep the transaction size smaller - hence smaller redo logs
> hence better overall database performance.
> So, for files larger in size (I think this applies to you) it is
> advisable to use BFILEs. A BLOB with large size will cause lot of
> log activity - slowing the transaction performance.
>
> 2. Continuing on the first point - since the database size is smaller
> (only the file pointer is stored), it helps in faster backup and
> recovery of the database. Please do not confuse this point with
> point#3 mentioned above. That is about "easier" backup and recovery
> for admins, this one is about "faster" backup and recovery.
>
> If you plan to use BLOBs in your case, I would recommend that you increase > the "log_buffer"
size to few MBs.

If we use BLOBs, it sounds like the issue with the redo log would create a performance problem for
us. What do you think?


Followup April 11, 2003 - 8am Central time zone:

it depends on your needs.

It is day 1. You load up your files. One day 2, your disk crashes.

What do you do? do you care you lost the files? if not, file system may be ok for you. If the
lost data causes you a problem -- sounds like you need a database.


o security
o backup, recovery
o single point of control (no chance someone will "accidently" erase the files)

Regards

Hemesh

No comments: