Re: Block level row structure

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)