Tiff Images and Oracle

Hello Friends.

We are re-designing our App and we have a critical question, what's the best way (in terms of performance) of using TIFF images (about 20K size) with Oracle.
Currently we have a Windows shared file server and we create the tiff images there under a huge directory structure (like /images/ddddmmyy/aa/bb/001, then /images/ddddmmyy/aa/bb/002, etc, etc). Our database is usually in LINUX version 10, 11 or 12.
We create about 200,000 images per day, keep them for 60 days and then remove that structure.
Our Web app (developed with .NET) reads those images just to display them on a Web Session (IE).

As you can see, what we are doing now works fine. But network sometimes is an issue and also it's hard to keep synchronization with our DR server, backups, etc.

Are we taking the correct approach? It would be better to have the images in CLOB or BLOBS for better performance? If so, can you provide me some suggestions on the best way to implement this?
As I mentioned, performance is the KEY FACTOR and the most important item to consider in this design.

Thanks,
Diego

If you have the advanced compression and partition options, then you could easily store the images as blobs in a range partitioned table by date. Then purging data would be as simple as dropping partitions. Blobs/Clobs can be space hogs, but advanced compression could allow you to compress the table which should help. I am not sure how easy it would be to compress the blob itself though.

Ultimately, the only way to know is to try it.

Adding to gandolf989's suggestion:

Put the range-dated partitions in their own equivalent (dated) tablespaces.

That way if you need to archive/move a partition, you can use "transportable tablespace" functionality (rman and datapump).
;)

Great idea!!! Thanks.
By any chance, do you have any code available to know how to start with that process?
Thanks a lot!

Quote:

---

Originally Posted by dpafumi

Great idea!!! Thanks.
By any chance, do you have any code available to know how to start with that process?
Thanks a lot!

---

If you want ddl, start by posting the ddl for the table that you want to partition.

Thanks again for the assistance.
My idea will be to create a table to hold the images like this:
CREATE TABLE Checks
(
Check_ID NUMBER(17),
filename VARCHAR2(50),
image BLOB,
CreatedDate date,
Accountnumber number(17)
);

As you mentioned, I could create partitions by CreatedDate.

Suggestions? The most important factor for me at this point is how to insert and retrieve those images.

Thanks again
Diego