What's the best way to save and retrieve binary files with Oracle 10g?

Question

I'm about to implement a feature in our application that allows the user to 'upload' a PDF or Microsoft PowerPoint document, which the application will then make available to other users in a viewer (so they don't get to 'download' it in the 'Save as..' sense).

I already know how to save and retrieve arbitrary binary information in database columns, but as this will be a commonly used feature of our application I fear that solution would lead to enormously large database tables (as we know one of our customers will want to put video in PowerPoint documents).

I know there's a way to create a 'directory' object in Oracle, but is there a way to use this feature to store and retrieve binary files saved elsewhere on the Database Server?

Or am I being overly paranoid about the database size?

(for completeness our application is .Net WinForms using CoreLab / DevArt OraDirect.Net drivers to Oracle 10g)

Thanks for your help :o)

Answer 1

Couple of options: You could put the BLOB column in its own tablespace, with its own storage characteristics; you could store the BLOBs in their own table, linked to the other table by an ID column. In either case as you suggested you could define the column as a BFILE which means the actual file is stored externally from the database in a directory. What might be a concern there is that BFILE LOBs do not participate in transactions and are not recoverable with the rest of the database.

This is all discussed in the Oracle 10gR2 SQL reference, chapter 2, starting on page 23.

Answer 2

I guess it depends what you consider enormously large.

It really does depend on the use case. If the documents are only being accessed rarely then putting it in the database would be fine (with the advantage of getting "free" backups, eg, with the database).

If these are files which are going to be hit over and over again, you might be better to put them directly on disk and just store the location, or even (if its really high bandwidth) look into something like MogileFS

No one is going to be able to give you a Yes or no answer for this.

Answer 3

You could use a normal LOB column type and set the storage parameters for that field so it's on a seperate tablespace. Create the tablespace somewhere that can handle having huge amounts of data thrown at it and you'll minimise the impact.

To be seriously super paranoid about disk usage you could additionally compress the tablespace by marking it as such. Something along the lines of:

CREATE TABLESPACE binary_data1 DATAFILE some_san_location DEFAULT COMPRESS STORAGE(...)

Answer 4

In my experience, a simple VARCHAR2 field containing the file name of the attachments is a better and easier solution. File system size is a lot easier to manage than database size.

Answer 5

The data has to live somewhere, whether it's internal to the DB or whether you just store a link to a (server) accessible file path, you're still chewing space.

I've just used simple LOB fields in the past, it seemed to work fine. If you keep the data inside the DB at least you keep your backup hassles low - you may have a lot of data to back up but when you restore it, it'll all be there. Splitting the binary out means you potentially break the DB or lose data if you're not careful about what you backup.

Answer 6

One reason to just store the link or an ID that can be used to build the link is that the storage that you usually use for Oracle DB's is rather expensive. If you have lots of large files, it is usually much more cost-effective to put them on a less expensive array of disks.