Home -> Mailing Lists -> Oracle-L -> Re: database of blobs

Re: database of blobs

From: Gerald Venzl <gerald.venzl_at_oracle.com>
Date: Wed, 7 Jun 2017 10:27:25 +0200
Message-Id: <77612864-6312-429B-AD45-246934242178_at_oracle.com> 








The design seems solid. In this scenario databases are often used to make the documents searchable via the metadata that is stored in the table next to the LOB, e.g customer, creation date, doc type, etc. 



Securefiles allow you to compress the LOB to save more disk space. 



You may want to partition on the creation date or something similar, assuming that those files are queried less when they get older. Then you can put an ILM strategy in place if you ever have to (compress older files with a higher compression ratio, etc). The creation date can be even defaulted to SYSDATE so that you don't have to worry about changing the INSERT statements. 



And, you may want to move to bigfile tablespaces. Nothing wrong with smallfile tablespaces, bigfile tablespaces just make the datafile management a bit easier. 



Thx,



Gerald


> On Jun 7, 2017, at 01:02, Jeff Chirco <backseatdba_at_gmail.com> wrote:
> 
> Yes they are securelob file
> 




>> On Tue, Jun 6, 2017 at 12:05 PM, Michael McMullen <ganstadba_at_hotmail.com> wrote:

>> Are you blobs stored as securefile lobs?

>> 

>> 

>> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jeff Chirco <backseatdba_at_gmail.com>

>> Sent: June 5, 2017 7:22 PM

>> To: oracle-l_at_freelists.org

>> Subject: database of blobs


>>  

>> Sort of a design question here.  I have a database that is only used to store documents or images in a blob field. This is mostly scanned copies of invoices or employee records as pdf's or jpeg images of pictures from some of our custom applications. Again all of these are stored in the same table as a blob field.  These blob's are currently 840gb in size in a small file tablespace that currently has 27 datafiles that are 32gb.  I potentially have the opportunity to reorganize this database and or table during an upcoming migration from 11.2.0.4 -> 12.1.0.2 and also moving Windows -> Linux.  

>> So I don't really have a direct question here but just wondering what anybody things about this, or is there something I might want to think about as this table grows, or maybe a better way to organize it.  I thought about maybe partitioning it somehow but it doesn't seem like there is a logical way to partition it.  Also there are no performance issues as all this database does is save and retrieve images.

>> 

>> Thanks,

>> Jeff

> 


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


Received on Wed Jun 07 2017 - 10:27:25 CEST












Original text of this message