Re: Block level row structure

Michael J. Moore wrote:
>
> 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)

Well, no bytes are used for the storage of the null values - just some bytes to indicate that there is a null there :-)

I think the book is probably referring to the "nulls at 'end' of row" concept.

cheers
connor Received on Mon Nov 17 2003 - 07:11:11 CST