RE: Storing blobs in database vs filesystem

There is one trade off of the CLOB compression that had not been touched here : a LOB under the size of 4K can be inlined into the table. Put this in conjunction with on the flight (de)compression and you realize that signature may be inlined.  

I don't know if you can inline a CLOB on an IOT overfolow area (need to check that) but it can certainly be an option to consider if your system is OLTP
and FTS are allowed. You get the advance of packed structure with remote storage of the CLOBS, while keeping it simple. I suppose also that you will go for a partitionned table, as it make it easy to archive.

I don't have the Java procedure, but I remember that the reason of the reduction in the DB growth from 10 to 3 TB was that they used a Java compression function on the CLOB, and that java was similar to gzip. Since gzip is open source, I suspect it should be easy to find. Unhappilly my Java knowlege is null.  

There has been absolutely not reduction in performance since the CLOB are meant to be accessed on single access,  

B. Polarski  

From: VIVEK_SHARMA [mailto:VIVEK_SHARMA_at_infosys.com] Sent: Friday, 29 September, 2006 12:01 PM To: Polarski, Bernard
Cc: ORACLE-L
Subject: RE: Storing blobs in database vs filesystem

Bernard, Folks  

We are also facing a similar issue with our "Banking" Application product where approx 1/3rd of the Database size is taken up by 1 Table which stores the Customer Signatures in one of its LONG/LOB fields.

Database Sizes are in the range of a few Tera Bytes.

The Banking Product is a primarily Pro C Application, with java functions too.  

Qs IS THE RESPECTIVE CLOB DATA ACCESSED BY USERS WHILE DOING OLTP TRANSACTIONS?   Qs IN OUR BANKING PRODUCT CASE, THE RESPECTIVE SIGNATURE IS VERIFIED AS PART OF AN OLTP TRANSACTION BY A BANK STAFF USER & THEREAFTER A CREDIT/DEBIT OLTP TRANSACTION IS PASSED TO THE RESPECTIVE CUSTOMER'S BANK ACCOUNT.   SOME ADDITIONAL QUESTIONS ARE EMBEDDED BELOW IN CAPITALS.   Thanks indeed  

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Polarski, Bernard Sent: Friday, September 29, 2006 12:25 PM To: strickland.mark_at_gmail.com; oracledba.williams_at_gmail.com Cc: dubey.sandeep_at_gmail.com; ORACLE-L
Subject: RE: Storing blobs in database vs filesystem  

I have been working on a 10 TB project. In mid course, it went down to 3

TB just by using a java compress function as an interface to store

in/serve out the CLOBS, whose algorithm is quite similar to gzip

algorithm to compress the CLOBS.  

• IF YOU COULD POSSIBLY SHARE YOUR JAVA COMPRESS FUNTION OR ALGORITHM? ANY LINKS, DOCS WILL HELP?
• IS THIS DIFFERENT FROM THE STANDARD ORACLE COMPRESSION FEATURE?
• HAS THERE BEEN A PERFORMANCE IMPACT WHEN ACCESSING YOUR COMPRESSED CLOBs? IF SO, HOW MUCH?

It made a huge save in SAN costing.

Though I am quite ignorant of the 25k java functions standard now in

10g, I wonder if there is not a java builtin version of gzip into latest

revisions of 10g.  

> Someone can confirm?

• AM ALSO INTERESTED IN THIS. B. Polarski

--

http://www.freelists.org/webpage/oracle-l

 

 

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended
solely for the use of the addressee(s). If you are not the intended
recipient, please notify the sender by e-mail and delete the original
message. Further, you are not to copy, disclose, or distribute this
e-mail or its contents to any other person and any such actions are
unlawful. This e-mail may contain viruses. Infosys has taken every
reasonable precaution to minimize this risk, but is not liable for any
damage you may sustain as a result of any virus in this e-mail. You
should carry out your own virus checks before opening the e-mail or
attachment. Infosys reserves the right to monitor and review the content
of all messages sent to or from this e-mail address. Messages sent to or
from this e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***
	

--
http://www.freelists.org/webpage/oracle-l