What are the ways to insert & retrieve BLOB data f

问题:

I've tried to insert BLOB data with SQL developer. But I can't find the insert statements which are actually used to insert BLOB data.

Apart from that, the database speed is really slow. For small files, it executes fine. But when I tried to import 50 mb avi file into BLOB, it took 3-4 minutes & still it was not completed. When I tried to export the BLOB data to a file, exporting process was also slow. I was using Oracle 10g Express Edition. If the database speed is slower than even file-system speed, then why database are used for storing BLOB data? Is there any other way to optimize the performance?

回答1:

First of all, you should expect storing BLOBs in a database to be (sometimes a bit, often significantly) slower, but definitly not faster than storing them in a file system. The reasons to store them in a DB do not center about performance, but about e.g.:

• Unavailability of a (shared) file system in a clustered or load-balanced scenario
• Ease of backup: Single process, a.o.t. 2 processes when files and DB are used
• Transaction safety: A BLOB is either there and complete or not, but not in a half-baked stage
• others I can't think of right now.

The general rule of thumb is, that if none of these concern you, you should store your files as ... files. Storing the metadata and pathname in a DB is IMHO good and common practice.

Concerning Oracle tuning: There are books written about that. I suspect to total them far over a ton in dead-tree-paperback format. You might first of all look at the Oracle process' memory consumption - rule of thumb: If it is less than a gig and you use BLOBs, you are in trouble. Read up on the different memory pools and how to increase them. Some limits for the express edition might apply.

回答2:

This is not a code you can use with your sql-developer, it's Java code that uses a StringBuffer in order to INSERT a blob. Hope it will be useful:

private void addBlob(oracle.jdbc.OracleConnection oracleConn, StringBuffer content) throws Exception
{    
      PreparedStatement st = null;
      try {                
            oracle.sql.BLOB blob = oracle.sql.BLOB.createTemporary(oracleConn, true, oracle.sql.BLOB.DURATION_SESSION);

            blob.setBytes(1, content.toString().getBytes("UTF-8"));

            st = oracleConn.prepareStatement("INSERT INTO MYTABLE (id, content) VALUES (MYTABLE_S.NEXTVAL, ?)");
            st.setBlob(1, blob);
            st.execute();
            st.close();
        }
        catch (Exception e) {
            utils.writeLog("Blob insertion Failed", e, utils.ERR);
            throw e;
        }        
        finally{
            st.close();
        }
  }