| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: NULLS when col is NOT NULL ??
On 26 Aug 2004 10:47:29 -0700, ed.prochak_at_magicinterface.com (Ed prochak)
wrote:
>Frank van Bortel <fvanbortel_at_netscape.net> wrote in message news:<cgk41j$uol$1_at_news5.tilbu1.nb.home.nl>...
>> Joel Garry wrote:
>>
>> > Ed Stevens <nospam_at_noway.nohow> wrote in message news:<6l2pi0pfsptulfkmdo2t48vn9bqt153mqi_at_4ax.com>...
>> >
>> >>On Tue, 24 Aug 2004 14:21:00 -0500, Ed Stevens <nospam_at_noway.nohow>
>> >>wrote:
>> >>
>> >>
>> >>So . . . to further clarify my understanding, given a column defined
>> >>as CHAR(8) (fixed length, not VARCHAR -- and nulls allowed), if x'00'
>> >>is not a 'representation' of nulls, what is physically stored in the 8
>> >>bytes reserved for that column?
>> >
>> >
>> > This ought to be even more confusing, as it appears '' and null are
>> > both octal 377 followed by a newline:
>> >
>>
>> [snip!]
>> The Oracle RDBMS will interpret '' as null, one of the non-ANSI
>> compliancies of Oracle. Looks like it's actually stored as high_values
>> (octal 377 being 0xff, or all 1's).
>
>
>And just to clarify a bit. the 0x00 in ASCII is spelled NUL
>ONLY ONE LETTER L.
>
>so NULL is relational DBMS speak for nothing
>and NUL is ASCII for the nul (0x00) character.
Block dumps might help here; Oracle does some decoding of the information so maybe it's hiding something, but I'm under the impression block dupms are fairly 'raw', at least for the row data parts.
SQL> create table t (c1 varchar2(8), c2 char(8));
SQL> insert into t values (null, null);
SQL> insert into t values ('a', 'b');
SQL> insert into t values (chr(0), chr(0));
SQL> insert into t values (null, 'a');
SQL> commit;
Commit complete.
SQL> @dump_blk -- (see: http://www.jlcomp.demon.co.uk/blockdum.html)
Enter value for m_offset: 1
old 3: header_block + &m_offset header_block
new 3: header_block + 1 header_block
Enter value for m_segment: T
old 7: segment_name = upper('&m_segment')
new 7: segment_name = upper('T')
Enter value for m_partition:
3 9994
old 1: alter system dump datafile &m_file block min &m_block block max
&m_block
new 1: alter system dump datafile 3 block min 9994 block max
9994
System altered.
And here's the row part of the dump, comments interspersed between the rows:
block_row_dump:
tab 0, row 0, @0x1f9d
tl: 3 fb: --H-FL-- lb: 0x0 cc: 0
This is (NULL,NULL) - just a row header with no data - cc (column count?) is zero here.
tab 0, row 1, @0x1f8f
tl: 14 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 1] 61
col 1: [ 8] 62 20 20 20 20 20 20 20
This is ('a','b') as varchar2,char.
Columns each start with the length, and then the column data.
CHAR's stored space padded.
tab 0, row 2, @0x1f81
tl: 14 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 1] 00
col 1: [ 8] 00 20 20 20 20 20 20 20
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.
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.
-- Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk> <http://www.andyhsoftware.co.uk/space> Space: disk usage analysis toolReceived on Thu Aug 26 2004 - 14:15:56 CDT
 |
 |