Re: Oracle 9i and null values

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Tue, 10 Jul 2007 14:30:14 -0500
Message-ID: <4693d229$0$10194$88260bb3@free.teranews.com>

poundboy_at_nyc.rr.com wrote:

>>From the concepts manual...

>
> Nulls are stored in the database if they fall between columns with
> data values. In these cases they require 1 byte to store the length of
> the column (zero).
>
> Trailing nulls in a row require no storage because a new row header
> signals that the remaining columns in the previous row are null. For
> example, if the last three columns of a table are null, no information
> is stored for those columns. In tables with many columns, the columns
> more likely to contain nulls should be defined last to conserve disk
> space.
>
> So looks like no matter what the datatype, a NULL value is represented
> by 1 byte.
>
> JR
>

A proof of concept...

Create a test table and populate it with data...including NULL values:

SQL> create table test (

   2 char_col char(10),
   3 varchar_col varchar2(10),
   4 num_col number(10,2),
   5 last_varchar varchar2(10));

Table created.

SQL> insert into test values ('abc',NULL,123,'xyz');

1 row created.

SQL> insert into test values (NULL,'abc',123,'xyz');

1 row created.

SQL> insert into test values ('abc','xyz',NULL,'bye');

1 row created.

SQL> insert into test values ('abc','xyz',123,NULL);

1 row created.

SQL> commit;

Commit complete.

Determine the file and block for this segment:

SQL> select file_id,block_id,bytes,blocks

2 from dba_extents where owner='BASIS' and segment_name='TEST';

FILE_ID BLOCK_ID BYTES BLOCKS

---------- ---------- ---------------- ----------
          5      33361           65,536          8

Dump the contents of the block:

SQL> alter system dump datafile 5 block 33361;

System altered.

Let's now look at the relevant portion of the dump file:

ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f44
avsp=0x1f2a
tosp=0x1f2a
0xe:pti[0]      nrow=4  offs=0
0x12:pri[0]     offs=0x1f81
0x14:pri[1]     offs=0x1f71
0x16:pri[2]     offs=0x1f5a
0x18:pri[3]     offs=0x1f44

block_row_dump:
tab 0, row 0, @0x1f81
tl: 23 fb: --H-FL-- lb: 0x1 cc: 4

col  0: [10]  61 62 63 20 20 20 20 20 20 20
col  1: *NULL*
col  2: [ 3]  c2 02 18
col  3: [ 3]  78 79 7a

tab 0, row 1, @0x1f71
tl: 16 fb: --H-FL-- lb: 0x1 cc: 4

col  0: *NULL*
col  1: [ 3]  61 62 63
col  2: [ 3]  c2 02 18
col  3: [ 3]  78 79 7a

tab 0, row 2, @0x1f5a
tl: 23 fb: --H-FL-- lb: 0x1 cc: 4

col  0: [10]  61 62 63 20 20 20 20 20 20 20
col  1: [ 3]  78 79 7a
col  2: *NULL*
col  3: [ 3]  62 79 65

tab 0, row 3, @0x1f44
tl: 22 fb: --H-FL-- lb: 0x1 cc: 3

col  0: [10]  61 62 63 20 20 20 20 20 20 20
col  1: [ 3]  78 79 7a
col  2: [ 3]  c2 02 18

end_of_block_dump
buffer tsn: 5 rdba: 0x01408259 (5/33369)

The "nrow=4" tells us this block contains four rows of data...which is to be expected. The line that with "block_row_dump:" tells us the start of each row of data in this table. We can see four rows of data. The "tl" value is the number of bytes for that row. So the first row of data looks like:

tab 0, row 0, @0x1f81
tl: 23 fb: --H-FL-- lb: 0x1 cc: 4

col  0: [10]  61 62 63 20 20 20 20 20 20 20
col  1: *NULL*
col  2: [ 3]  c2 02 18
col  3: [ 3]  78 79 7a

We can see the NULL value in column 1 (the second column of the table,VARCHAR_COL). If we compare this to the last row, we can see that Oracle is not storing the NULL value when it occurs at the end of the row:

tab 0, row 3, @0x1f44
tl: 22 fb: --H-FL-- lb: 0x1 cc: 3

col  0: [10]  61 62 63 20 20 20 20 20 20 20
col  1: [ 3]  78 79 7a
col  2: [ 3]  c2 02 18

Also notice that the number of bytes for this row is 22 bytes, or one less than if the NULL is stored in the middle of the row. So this should serve to prove that the NULL values does indeed take 1 bytes of storage unless it comes at the end of the row.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com

Received on Tue Jul 10 2007 - 14:30:14 CDT