7 Replies Latest reply: Feb 1, 2013 8:36 AM by Osama_Mustafa RSS

    Should heavy binary files be stored in database?

    user521219
      I was asked an interesting question: Should a database contains all data? Or heavy binary files should be stored in file system?

      Example of heavy binary files : videos or heavy pdf files (+200 MB).

      With an old aspx web app (1.1) I tried to open a 200MB pdf file stored as a blob in an Oracle 11g database, and it just run out of memory.

      However, same asp.net web application had no problem to open same pdf file stored in file system of a server. It could be that maybe there is some proper way to open heavy blobs fields with asp.net.

      For integrity reasons, I say that all data should be stored in database, but my described case showed me that maybe it's not the way.

      SQL Server allows the contents of varbinary(max) columns to be stored on the file system, maybe there is something similar in Oracle?
        • 1. Re: Should heavy binary files be stored in database?
          P.Forstmann
          You can store data as operating system files with BFILE data type: http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_intro.htm#i1006165 but BFILE are read only in database.

          If you do so you must make sure to have the right backup and recovery tools to be able to synchronize database contents and BFILE datas when restoring/recovering database.

          Edited by: P. Forstmann on 28 janv. 2013 21:52
          • 2. Re: Should heavy binary files be stored in database?
            Mark Malakanov (user11181920)
            With an old aspx web app (1.1) I tried to open a 200MB pdf file stored as a blob in an Oracle 11g database, and it just run out of memory.
            It could be that maybe there is some proper way to open heavy blobs fields with asp.net.
            It depends very much how to open BLOB.
            A simple way is to get whole value into array (like getArray()). It may be risky when there are big values.
            More sophisticated way is to read it by chunks.
            In programming environments like Java or ASP.net should be methods that open BLOB as a data stream. In Java it is ResultSet.getBinaryStream()
            You can pass this stream further, for example to browser connection. It will read BLOB value by little chunks (like by 64K) and pass it to browser or whatever client.
            • 3. Re: Should heavy binary files be stored in database?
              Osama_Mustafa
              nice question since it has been asked before on this forum and answered by experts here you can search on this forum about that also check the below Link
              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1011065100346196442
              • 4. Re: Should heavy binary files be stored in database?
                Billy~Verreynne
                user521219 wrote:

                With an old aspx web app (1.1) I tried to open a 200MB pdf file stored as a blob in an Oracle 11g database, and it just run out of memory.
                Because it likely tried to read, and cache, the entire BLOB. Instead of reading and streaming BLOB chunks to the (web browser) client.
                However, same asp.net web application had no problem to open same pdf file stored in file system of a server. It could be that maybe there is some proper way to open heavy blobs fields with asp.net.
                So now blame Oracle for the inability of an ASP/.Net programmer to stream file contents correctly, but not LOB contents?
                SQL Server allows the contents of varbinary(max) columns to be stored on the file system, maybe there is something similar in Oracle?
                You want Oracle to be like SQL-Server? Oracle does a poor SQL-Server imitation. It plays the role of being Oracle, technically the most advance relational database management system available, excellently.
                • 5. Re: Should heavy binary files be stored in database?
                  CoBy
                  Hello,

                  We are storing GB's of files directly in an oracle database and have no problem with read/write operations. I think 200MB should not be a problem. Check the application code.
                  You can store directly in LOB's in a table(s) or use BFILES.
                  Maybe you want to have a look at the dbms_lob documentation.

                  Best regards,
                  Coby
                  • 6. Re: Should heavy binary files be stored in database?
                    user521219
                    Coby,
                    Could I ask what methods you use in your program for opening BLOB values ?
                    Do you open it as data stream (as Mark's suggestion) and reading it by little chunks?
                    • 7. Re: Should heavy binary files be stored in database?
                      Osama_Mustafa
                      actually there's impact of storing Big Files in Database increase the size of the database , Heavier load on the database server , Latency and DB backups/restore will take lot longer.