I know I could just check it with a table, but it might be faster to
just ask. :>

When I have a table that has several BLOB (or CLOB) fields, can I count
on them occupying zero bytes (or a handful) when these fields contain
nothing? (or do they start with a minimum of one blob block size, even
if empty?)

Thx!

Dominique


I'm not sure how ElevateDB is structured but I think what will happen is a pointer in the table file for each blob/clob field with no data in the blob file

Roy Lambert [Team Elevate]

Roy Lambert wrote:
> I'm not sure how ElevateDB is structured but I think what will happen
> is a pointer in the table file for each blob/clob field with no data
> in the blob file

I'm sure that's what happens. Or with the pointer simply being null
when no data.

If anyone knows it to be different, I'm sure they'll chime in.

Have not done this for a while but i as recall the file storage in the
main table file remains constant (it's like Roy said just a pointer so
likely a int or maybe 64bit int field anyways).

Actual blob data is in the EDBBlb file that does have a minimum size
even when blank but it's tiny (like 1KB or such) and will only grow if
you actually have data in the CLOB/BLOB.


If you're really concerned about storage then assume worst case that
main table stores the full pointer value even if null in main table but
that's all there will be.

Raul




On 10/31/2012 12:30 PM, Dominique Willems wrote:
> Roy Lambert wrote:
>> I'm not sure how ElevateDB is structured but I think what will happen
>> is a pointer in the table file for each blob/clob field with no data
>> in the blob file
>
> I'm sure that's what happens. Or with the pointer simply being null
> when no data.
>
> If anyone knows it to be different, I'm sure they'll chime in.
>

Raul wrote:
> Actual blob data is in the EDBBlb file that does have a minimum size
> even when blank but it's tiny (like 1KB or such) and will only grow
> if you actually have data in the CLOB/BLOB.

That would be very bad news. Since I have 512 as BLOB block size, 1 KB
(or 512 bytes) would be huge. It concerns fixed one-time created
records that sometimes do have data in their blob fields but in the
vast majority of cases do not. Having each time 512 extra bytes per
record per blob field would increase the database size with a huge
factor, especially when millions of records are concerned.

That's why I asked the question. Maybe I do need to just add additional
tables instead of using the blob fields.

I'm pretty sure it's not per record but per table - blob table has a
minimum size.

Adding records to main table with NULL blob should not grow the blob
file at all so the only space cost would be in main table (again in
worst case is pointer size per record).

Raul

On 10/31/2012 12:54 PM, Dominique Willems wrote:
> That would be very bad news. Since I have 512 as BLOB block size, 1 KB
> (or 512 bytes) would be huge. It concerns fixed one-time created
> records that sometimes do have data in their blob fields but in the
> vast majority of cases do not. Having each time 512 extra bytes per
> record per blob field would increase the database size with a huge
> factor, especially when millions of records are concerned.
>
> That's why I asked the question. Maybe I do need to just add additional
> tables instead of using the blob fields.
>

Raul wrote:
> I'm pretty sure it's not per record but per table - blob table has a
> minimum size.

Exactly. I misread your first post thinking you meant a minimum size
per record.

Cheers!

Raul

>Adding records to main table with NULL blob should not grow the blob
>file at all so the only space cost would be in main table (again in
>worst case is pointer size per record).

Its not worst case its just what is. Since ElevateDB is fixed length then the space for the pointer is allocated on a per row basis. Doesn't matter if its full of NULL, zero or some integer its still taken up on disk.

Roy Lambert [Team Elevate]