Re: Foreign key in Oracle Sql

"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. Received on Fri Jan 21 2005 - 11:25:51 CET