Re: Improving LONG RAW retrieval performance.

musem_at_westworld.com (Bob Stewart) wrote:
>This is probably an unacceptable answer, but have you considered whether
>Oracle is really the right vehicle to store something like this? Wouldn't
>it be far easier to manage, and likely quicker, to just have Oracle store
>a fileid, and keep the TIF image in your OS file system? If you OS
>supports permissions, you shouldn't have to worry too much about unauthorized
>remove of the files. If your OS supports mmap, you can get all the
>advantages of a memory image, without the time wastage of storing and
>retrieving the complete 250K image into memory; which, by the way, is what
>you have to do when using a LONG RAW for this application.

I've done this in the past quite effectively. The problems associated with using external (file system) storage and simply tracking the name in the column that I encountered are:

1. You can't coordinate the file set with forward and backward merge operations. So if you have to restore from backup or regress the database, your file system content is out of sync.
2. You can't apply transactionalization logic or 2-phase commit. The most often occurring consequence is that you end up with files that have no matching entry in the relational table.
3. Any form of disk failure in the file system where the "raw" data is being stored is unrecoverable (from a DBMS perspective). So you have to carefully address any "impedance" mismatches between the file system backup and recovery mechanisms and the relational DBMS backup and recovery mechanisms.
4. You can't apply hierarchical storage management techniques for pruning the active tables, with the inactive content being managed by a back-store device.
5. You end up having to explicitly write access mechanisms for distributed clients to retrieve the files, and they are _very_ different from typical. In our case, our clients use ODBC predominantly. So we had to develop a mechanism for retrieving the "raw" data stored in the files to be used in addition to the standard ODBC retrieval techniques built into each of the client development products.
6. Your only access mechanism for the file is via the tuple; i.e. no selection keys.

These are not to denigrate the method; unfortunately Oracle's support for large binary objects is shallow at best (as is also true of each of the other relational DBMS vendors that I've checked) so work-arounds of this type become necessary. The last time that I did this, I had to write:

• file retrieval methods for distributed clients (based upon embedding the ftp protocol).
• an orphan file clean-up utility.
• an orphan tuple reporting utility.
• a backup and restore utility that would synchronize with the R:DBMS.
• a system initialization utility that was started by init(8) and scanned to check the availability and integrity of the file system area where the files were stored prior to starting the R:DBMS.
• stored procedures to validate the filename's adherence to OS naming conventions prior to attempting the creation.
• a daemon that receives a "tap on the shoulder" when a filename is inserted or changed from the trigger, and which then retrieves the file from the client.
• there may have been other details as well; I just can't remember them now.

So the key thing is that this method may provide an acceptable alternative, but it may also back you into quite a load of maintenance work and the development of related (unexpected) utilities.

One approach that may help is the use Transarc's structured file system in this context. I believe that SFS can be transactionally synchronized with several relational DBMS systems. So SFS files and tuples could be more closely coordinated. Also, SFS supports keys so you could support the notion of foreign keys relating the file to one or more tuples.

You might also consider badgering your R:DBMS vendor for better "blob" support in the first place. This is not a new requirement. ;-) Mark A. Scarton, ABD
CompUtah!, Park City, Utah USA
Home: 801.565.9835
Office: 801.265-4612 Received on Mon Mar 04 1996 - 00:00:00 CET