Re: performance using UPDATE vs DELETE/INSERT

"Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:FCWmd.29238$KJ6.17951@newsread1.news.pas.earthlink.net... > Hardware? Not that it makes much difference. 7.31 on Solaris x86 on an old Proliant. We're moving to 9.40 on a new Proliant under Linux. > Are the BYTE blobs in a blob space or 'IN TABLE'? If you don't say, > they're IN TABLE. They are in table space now because they are being replicated offsite. When the offsite system has extracted the binary, the record is marked to have it removed from the record locally. When we move to 9.40, we will eventually move that field to smart blob space so it can be replicated. > If IN TABLE blobs are 20 MB or so, then each blob occupies 10,000 or so > pages - quite a lot. You'd probably do better with a blob space > configured with a larger page size. Blobs in blob spaces don't thrash the > logical log; blobs IN TABLE do. Everything is logged and processed within transactions, so the logical logs get thrashed anyway. We have about 750 5Meg logs. > However, just as with anything else, once the disk space associated with > blob is allocated to the table, that extent is not released for other > tables to use - though the table will reuse it when it needs more space. The home office contends that the space becomes unusable. Extents are allocated to the table and no other table can get to it -- tat I understand. What I could not grokk was the idea that when an update causes a record to shorten by many thousands of pages that those pages go into a limbo of sorts. from what you;ve said, I conclude that they are wrong on this. > I would not worry too much about the fragmentation within an extent. If I > was going to worry about it, I'd be splitting blobs into a blob space, and > indexes would be detached. The new version (in 9.40) will split off the indices and blobs into other dbspaces. That should help in every case. Thank you for your help! -- DCP