Re: Oracle glob/blob handling

Hi,

AcCeSsDeNiEd schrieb:
> Hi there.
>
> I'm pretty new to Oracle so plz no flames :)
>
> I have many small files measuring btw 200kb - 500 kb each.
> I expect them to grow to about 20-30 gig a year in total.
>
> I intend to insert them as a glob/binary into the Oracle db.
> Putting them into directories and then linking/refrencing to them is
out 'cos of the complexity of
> the database design and the flexibility that is needed in the future
to move the files from record
> to record.
>
> 1. Can Oracle handle this? I means in terms of speed & record searches
& file retrievals?
> 2. Won't it slow down with such a huge load of binary data?
> 3. Will it get easily corrupted?

About two years ago we realized a prototype for testing an application with similar demands: We have a throughput of about 40 millions small blobs (each up to 15 KB) per year through the database.

In our prototype we tested with Oracle 8.1.5. Due to disk space restrictions we stored up to 15 millions of blobs. We could not find any problems with Oracle (we had problems with the server bios of the Siemens box ;-) ). We inserted about 10000 records per minute (each insert fired a trigger with an insert into internal structures) and selected about 15000 records per minute. Using multiple clients increased the througput. Under Linux as server OS Oracle was about 20% bit faster than under Windows.

All blobs were stored in a single table. It was stored in an own tablespace on an own hd drive. We did not use any partitioning of tables.

Our environment:
2-tier architecture
Server and Client OS: Windows, later server OS Linux Oracle: 8.1.5
Client: Own Delphi application
Network: 100 MBit
Server hardware:
2 500 MHz XEON
2 - 4 GB RAM (did not really change the performance) own hd drivess for OS, TEMP, SYSTEM, INDEX, USER, BLOBS

We did not make any operations inside the blobs like text searching or so. For us the blobs were just binary coded data which should be stored inside the db and at a given we selected, transferred it to a production machine and after all that we deleted it.

We recommended our customer to use Oracle under Linux. Unfortunately Oracle changed its pricing scheme during the decision phase. With the new scheme, Oracle became to expensive for our customer and therefore we switched to another db system. Additional information: MS SQL-Server 7 did not fit on the same hardware. It was much to slow and had a lot of painful restrictions regarding our needs.

My 2 cent:
Please read and understand the concept of Oracle's tablespaces and how they are used. With your demands you can gain a lot of performance with a proper physical design. You find it in the 'Server Concepts Guide' (file a96524.pdf in OTN).

-- 
All emails sent to this address are never read and never will be
answered. Sorry, but until someone cleans up the spam mess, that's the
way it has to be.

E-Mails, die direkt an diese Adresse geschickt werden, lese und
beantworte ich nicht. Ich bedauere diesen Umstand sehr, kenne derzeit
aber keine bessere Möglichkeit, um die Spam-Flut abzustellen.

Mit freundlichen Grüßen / Best Regards
Michael Gast
SEPP MED GmbH