Re: Block level row structure

Hmmm
It says on the OCP Oracle9i Database: Fundamentals I Exam Guide on page 256 ...
"If a column value is NULL ...no bytes are used for the storage of NULL column values."

I shouldn't be surprised considering I have counted over 30 errors in this book!

Thanks for the real story.
Regards,
Mike

"Andy Hassall" <andy_at_andyh.co.uk> wrote in message news:gqefrv44if014kpre5sqad8rb71lroe11v_at_4ax.com...
> On Sun, 16 Nov 2003 16:27:31 GMT, "Michael J. Moore"
> <NOhicamelSPAM_at_comcast.net> wrote:
>
> >I understand that there is a row header that contains a count of the
number
> >of rows in the record. There is also a 1 or 3 byte length field for each
> >field in the row, unless the field is NULL in which case there is nothing
> >there. So, given a table with two columns defined as EL1 NUMBER and EL2
> >NUMBER, how can Oracle tell if the value stored in a given row represents
> >EL1 or EL2 when one value is NULL and the other is not?
> >
> >From my understand, all it would know is that it has 2 fields, and one
data
> >value, but would not know if that data value is for EL1 or EL2. Obviously
I
> >am missing a piece of the puzzle.
>
> Where there is a null before any non-null column values, it is stored,
taking
> one byte, for the reasons you mention.
>
> See http://www.jlcomp.demon.co.uk/blockdum.html for how to make block
dumps to
> have a look at the structure.
>
> SQL> create table nullstorage (n1 number, n2 number);
>
> Table created.
>
> SQL> insert into nullstorage values (1, 2);
>
> 1 row created.
>
> SQL> insert into nullstorage values (1, null);
>
> 1 row created.
>
> SQL> insert into nullstorage values (null, 1);
>
> 1 row created.
>
> SQL> insert into nullstorage values (null, null);
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select header_file, header_block + 1
> 2 from dba_segments
> 3 where segment_name = 'NULLSTORAGE'
> 4 and owner = user;
>
> HEADER_FILE HEADER_BLOCK+1
> ----------- --------------
> 5 37786
>
> SQL> alter system dump datafile 5 block min 37786 block max 37786;
>
> System altered.
>
>
> From the dump file produced:
>
> block_row_dump:
> tab 0, row 0, @0x1f97
> tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
> col 0: [ 2] c1 02
> col 1: [ 2] c1 03
>
> [This is the (1, 2) row - both columns populated and stored]
>
> tab 0, row 1, @0x1f91
> tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
> col 0: [ 2] c1 02
>
> [This is the (1, null) row - the trailing NULL is not stored]
>
> tab 0, row 2, @0x1f8a
> tl: 7 fb: --H-FL-- lb: 0x1 cc: 2
> col 0: *NULL*
> col 1: [ 2] c1 02
>
> [This is the (null, 2) row - the leading NULL is stored, taking up 1
byte]
>
> tab 0, row 3, @0x1f87
> tl: 3 fb: --H-FL-- lb: 0x1 cc: 0
>
> [This is the (null, null) row - neither column is stored]
>
> end_of_block_dump
> End dump data blocks tsn: 5 file#: 5 minblk 37786 maxblk 37786
>
> --
> Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
> Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Received on Sun Nov 16 2003 - 14:33:35 CST