BLOB Performance

Hi,

My application stores JPEG images in a LONG RAW field under Oracle 7.3 and I'm having a problem with performance. The application must frequently perform a full table scan of the table containing the BLOB, retrieving other fields from the table, not the BLOB itself. Currently the table contains some 10,000 entries and was initially populated with only the text data, not the BLOB data. At that time a full table scan took around 1 second. Once the BLOB data was added, JPEG images of 20k to 30k each, totalling some 250M, the full table scan performance dropped to around 1 minute. Even just running SELECT COUNT (*) from this table now took some 10 - 15 seconds!

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.

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?

Regards,
Max Andre Busch.

mbusch_at_tds.ie Received on Sat Aug 12 2000 - 00:00:00 CDT