Re: BLOB Performance

On Sat, 12 Aug 2000 20:54:13 +0100, "Max Busch" <mbusch_at_tds.ie> wrote:

>Is there any way of improving BLOB storage to get the performance back,
>without affecting the database design? An obvious solution that springs to
>mind is to store the BLOB in it's own table, but my company does not control
>the database design and I don't want to request a potentially costly
>database change just yet.

Do. That's the solution.
Increase the block size of the database to around 16K (you have to re-create the database for this!). Then move the BLOB stuff to its own table with a single PK sequence-generated column and the BLOB. FK to it from the rest of the info in another table.

>
>I also noticed that, after populating it with the BLOBs, the table had 25
>extents and it's indexes had 9 extents each. Also I was told that it might
>suffer from chaining. Could this alone explain the performance drop?
>

Yes. 30K images WILL cause chaining. And chaining WILL cause serious degradation in full table scans.

There are other solutions with the latest versions of ORACLE, but this should get you going without major upgrade traumas.

Cheers
Nuno Souto
nsouto_at_nsw.bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Sun Aug 13 2000 - 00:00:00 CDT