What is the best way to save and retrieve binary files using Oracle 10g?

Question

I’m going to implement a function in our application that allows the user to “download” a PDF or Microsoft PowerPoint document, which the application will then make available to other users in the viewer (so that they don’t get to “download” it in “Save As ...”) .

I already know how to store and retrieve arbitrary binary information in the database columns, but since this will be a widely used function of our application, I am afraid that this solution will lead to extremely large database tables (since we know that one of our customers will want to put the video in PowerPoint documents).

I know that there is a way to create a directory object in Oracle, but is there a way to use this function to store and retrieve binary files stored elsewhere on the database server?

Or am I too paranoid about the size of the database?

(For completeness of our application, there is .Net WinForms using the CoreLab / DevArt OraDirect.Net drivers in Oracle 10g)

Thanks for your help: o)

Answer 1

A couple of parameters: you can put the BLOB column in your own tablespace with your own storage characteristics; you can store blobs in your table, bound to another table by an identifier column. In any case, as you suggested, you can define the column as BFILE, which means that the actual file is stored externally from the database in a directory. What could be the problem is that BFILE LOBs are not involved in transactions and are not restored along with the rest of the database.

This is stated in the SQL reference 10gR2 SQL, Chapter 2, starting on page 23.

Answer 2

I think it depends on what you consider extremely large.

It really depends on the use case. If documents are rarely accessed, then their placement in the database will be in order (with the advantage of receiving “free” backups, for example, with a database).

If these are files that will be repeated over and over, you might be better off placing them directly on the disk and just saving the location, or even (if its really high bandwidth) would look something like this: MogileFS

No one will be able to give you the answer "Yes" or "no" for this.

Answer 3

You can use the regular LOB column type and set storage options for this field so that it is in a separate table space. Create a table space somewhere where you can process a huge amount of data that you have chosen, and you minimize impact.

To be seriously super paranoid about disk usage, you can further compress the table space by marking it as such. Sort of:

CREATE TABLES binary_data1 DATA FILE some_san_location DEFAULT COMPRESS STORAGE (...)

Answer 4

In my experience, a simple VARCHAR2 field containing an attachment file name is the best and easiest solution. File system size is much simpler than database size.

Answer 5

The data should live somewhere, regardless of whether it is internal to the database or just keep a link to an accessible path to the file (server), you are still chewing space.

I just used simple LOB fields in the past, it worked fine. If you store data inside the database, at least you save your backup problems - you may have a lot of data to back up, but when you restore it, everything will be there. Splitting the binary means that you can break the database or lose data if you are not careful about what you are doing backups.

Answer 6

One reason to just keep the link or identifier that can be used to build the link is that the storage you usually use for Oracle DB is quite expensive. If you have many large files, then, as a rule, it is much more economical to place them on a less expensive disk array.