Subject Re: [Firebird-Architect] blobs causes table fragmentation.
Author Pavel Cisar

On 4 Oct 2004 at 10:44, Dmitry Kuzmenko wrote:

> Hello, All!
> As I understand current behavior, blobs less than 256 bytes are
> stored at the same data page, in record. If blob is bigger it
> is stored at blob page, not data page.
> There are some systems that store blobs with different size,
> so blob data is spreaded between data and blob pages.
> This makes table more "fragmented" and seriously slowdown
> record retrieval if 'select' does not selects blob fields.

In fact, non-null blob values always have some structure stored as
separate records on data page. For level 0 blobs, i.e. small ones that
fit on datapage, the record contains blob data, while for level 1 and 2
blobs it contains vector of blob-page numbers.

> The suggestion is - to add some header page flag that
> will disable blob storing at data page.

With current architecture for blobs, you can't save record slots that
way. Of course, record itself would be smaller, because for small blobs
it would contain only one blob page number. So what you propose is a flag
that would force all level 0 blobs to be stored as level 1. Should be
quite simple to implement.

The problem is how at what level this flag would be defined ? At table,
database or server level ? For table-level flag, it means new field in
rdb$relations to store it. Not hard on itself, but how this value would
be set by developer ? I really dislike any extension to SQL, and config
file (if we would take Jim's cascade config files as future Firebird
standard) is good for database and server level options, not for table-
level ones. I also dislike direct updates to system table performed by
user. That really doesn't leave any options for table-level option, at
least none I can see. Could be quite simple at database/server level via
cascading config file(s), but I'm not sure how useful it would be then.

> Of course, this flag will cause small blobs to be at the whole
> blob page, and db space will be less used. But anyway, this
> will speedup record retrieval.

There is always some sort of trade-off, so this doesn't trouble me. But
what does is that it would introduce new config (or whatever) parameter
to be tweaked badly, and force us one step closer to the bloat of Oracle
and the likes.

Best regards
Pavel Cisar (ICQ: 89017288)
For all your upto date Firebird and
InterBase information