Re: Foreign key in Oracle Sql
From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 21 Jan 2005 10:47:51 -0800
Message-ID: <41f14e4b$1_1_at_127.0.0.1>
>
>
> I'd like to respond to your request for TECHNICAL reasons, without wasting
> too much time on the fray part of it.
>
> There are several ways to distinguish NULL markers from real data. Two of
> them are the bit array and the impossible value flag. The bit array uses a
> separate vector for bits that mark the presence of data. This adds
> overhead, at the rate of one byte for every eight columns in the tuple. A
> column with a zero in the bit string for a tuple has a NULL in that
> position. End of discussion.
>
> The "impossible values" technique relies on the existence of a bit pattern
> in the data itself that can never correspond to a meaningful value. That's
> how Oracle did it. There's less overhead than the bit string. What did
> Oracle, for instance, do about the NUMBER datatype? Well, given the way
> they represent numbers internally, there's this representation that might
> be called "minus zero". This representation can't be stored. If you
> compute something that comes out to zero, it's going to be stored, in
> Oracle, as plus zero, regardless. So this "extra representation" was used
> to indicate a NULL for the NUMBER datatype. See how neatly it works?
>
> Now, how about fixed length character strings? Simple. Just fill the whole
> field (oooops I meant attribute... ignore that man behind the curtain!)
> with zero bytes. A zero byte is the Ascii Null character anyway, so this
> might as well be the SQL NULL form. This sounds like it wastes a lot of
> space, but Oracle has other neat tricks it does to deal with that.
>
> Now, how about variable length strings? Well, the people in charge of
> NULL handling in Oracle decided that a single byte with value zero would be
> a great way to represent a NULL instead of a character string. The people
> in charge of character string operations in Oracle decided that a single
> byte with value zero would be a great way to represent the character string
> of length zero.
>
> Oops!
>
> Now, whether it gets interpreted as a value or a NULL depends on which part
> of Oracle is processing the value. That's why you see the inconsistencies
> that others have pointed out in this newsgroup. It's also why it would
> involve some engineering effort to fix it. But the biggest issue is neither
> the cost nor the benefit, IMO. It's all the existing code with things like
>
> NVL (Spouse_Name, 'N/A') in it. This stuff might break if Oracle were
> fixed.
>
> As to whether Oracle really should fix it, that's a business decision.
>
> As to whether Oracle could have avoided the pitfall in the absence of SQL
> standards, that's a discussion on another level. I'm prepared to discuss
> that, but I want to answer the technical questions first.
Date: Fri, 21 Jan 2005 10:47:51 -0800
Message-ID: <41f14e4b$1_1_at_127.0.0.1>
David Cressey wrote:
> "TML" <joeysmith_at_gmail.com> wrote in message
> news:1106275090.342389.314890_at_f14g2000cwb.googlegroups.com...
>
>
>>I'd like to hear more about this. Why not? Why *couldn't* Oracle >>implement a runtime compatibility setting for this? Is there an actual >>TECHNICAL reason, or simply the obvious (and already discussed) slew of >>business reasons?
>
>
> I'd like to respond to your request for TECHNICAL reasons, without wasting
> too much time on the fray part of it.
>
> There are several ways to distinguish NULL markers from real data. Two of
> them are the bit array and the impossible value flag. The bit array uses a
> separate vector for bits that mark the presence of data. This adds
> overhead, at the rate of one byte for every eight columns in the tuple. A
> column with a zero in the bit string for a tuple has a NULL in that
> position. End of discussion.
>
> The "impossible values" technique relies on the existence of a bit pattern
> in the data itself that can never correspond to a meaningful value. That's
> how Oracle did it. There's less overhead than the bit string. What did
> Oracle, for instance, do about the NUMBER datatype? Well, given the way
> they represent numbers internally, there's this representation that might
> be called "minus zero". This representation can't be stored. If you
> compute something that comes out to zero, it's going to be stored, in
> Oracle, as plus zero, regardless. So this "extra representation" was used
> to indicate a NULL for the NUMBER datatype. See how neatly it works?
>
> Now, how about fixed length character strings? Simple. Just fill the whole
> field (oooops I meant attribute... ignore that man behind the curtain!)
> with zero bytes. A zero byte is the Ascii Null character anyway, so this
> might as well be the SQL NULL form. This sounds like it wastes a lot of
> space, but Oracle has other neat tricks it does to deal with that.
>
> Now, how about variable length strings? Well, the people in charge of
> NULL handling in Oracle decided that a single byte with value zero would be
> a great way to represent a NULL instead of a character string. The people
> in charge of character string operations in Oracle decided that a single
> byte with value zero would be a great way to represent the character string
> of length zero.
>
> Oops!
>
> Now, whether it gets interpreted as a value or a NULL depends on which part
> of Oracle is processing the value. That's why you see the inconsistencies
> that others have pointed out in this newsgroup. It's also why it would
> involve some engineering effort to fix it. But the biggest issue is neither
> the cost nor the benefit, IMO. It's all the existing code with things like
>
> NVL (Spouse_Name, 'N/A') in it. This stuff might break if Oracle were
> fixed.
>
> As to whether Oracle really should fix it, that's a business decision.
>
> As to whether Oracle could have avoided the pitfall in the absence of SQL
> standards, that's a discussion on another level. I'm prepared to discuss
> that, but I want to answer the technical questions first.
Well done ... and thanks.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace 'x' with 'u' to respond)Received on Fri Jan 21 2005 - 19:47:51 CET