Re: NULLS when col is NOT NULL ??

Andy Hassall <andy_at_andyh.co.uk> wrote in message news:<djcsi0tfi5vjagqshq8a76p930om09ea3o_at_4ax.com>...
>
> Here's (chr(0), chr(0)).
> So NUL is stored as a byte for the length [1], and then a zero byte.
> For CHAR it's then space padded.

That's what I thought was so interesting with od, the char appeared not actually padded in the actual physical data. I had wondered about this because I work on a packaged-for-various-database-engines system that uses char all over the place, but doesn't seem to waste the space that one would expect if they were truly padded.

Of course, I may be truly insane or even just confused.

>
> tab 0, row 3, @0x1f74
> tl: 13 fb: --H-FL-- lb: 0x1 cc: 2
> col 0: *NULL*
> col 1: [ 8] 61 20 20 20 20 20 20 20
> end_of_block_dump
>
> It's been replaced by *NULL* here, but I believ an 'embedded' NULL gets stored
> as a single byte zero ('trailing' NULLs are just eliminated by cc being less
> than the number of cols in the table as in the first row). This looks like why
> Oracle treats '' and NULL as the same, as their stored equivalents would be the
> same:
>
> NULL: One byte, value zero.
> '' : The length of '' is zero, so store zero in the length field, then
> there's no more data to store - so it looks identical to NULL's representation.

I didn't even think of that, I was just putting leading and trailing field values to make the null character easy to find. This suggests more tests like having a bunch of nulled fields followed by stuff, and not followed by stuff. Wish I had more time to play with this, esp. comparing block dumps to od. With millions of rows, nulls can add up.  :-)

jg

--
@home.com is bogus.
President of DMA doesn't like spammers! 
http://www.signonsandiego.com/uniontrib/20040827/news_1b27internet.html