RE: NULL value

Message-Id: <10645.118905@fatcity.com> From: "Koivu, Lisa" Date: Tue, 10 Oct 2000 16:17:18 -0400 Subject: RE: NULL value This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C032F7.1710E80E Content-Type: text/plain; charset="iso-8859-1" Mladen, from the class kit: There is no index entry corresponding to a row that has all key colunmns that are NULL. Bitmap indexes do store null entries in a way, since they store 1's and 0's - yes and no for all the values in the column. So it seems to me, saying IS NULL or IS NOT NULL in a query does not use an index to retrieve the data, it becomes a full table scan, because it has nothing to work with. Am I wrong? Lisa Rutland Koivu Oracle Database Administrator Qode.com 4850 North State Road 7 Suite G104 Fort Lauderdale, FL 33319 V: 954.484.3191, x174 F: 954.484.2933 C: 954.658.5849 http://www.qode.com "The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com." -----Original Message----- From: Gogala, Mladen [mailto:MGogala@oxhp.com] Sent: Tuesday, October 10, 2000 12:05 PM To: Multiple recipients of list ORACLE-L Subject: RE: NULL value It's stored in the column because NULL != NULL. The only operators that can be applied to the NULL value are IS and IS NOT. Nothing else. -----Original Message----- Sent: Tuesday, October 10, 2000 7:26 AM To: Multiple recipients of list ORACLE-L Hi, I have a logical question, any one who can make it clear. create table a (x number(2) unique); insert into a values (null); 1 row created. insert into a values (null); 1 row created. Q:- I have a table 'A' have one field 'X' with unique constraint, so a unique index will be created for it. I inserted null into this field two times (or more then it). If null is a value, Why these null stored into table which have unique constraint? If null is not a VALUE then what it is? Is there some special character for NULL? If null is not a value why we have operators for null? Regards Muhammad Awais System Developer Paktel Ltd. Pakistan Mob. 0303 7757077 (92-51) 111-222-111-282 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: awaisb@paktel.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: MGogala@oxhp.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C032F7.1710E80E Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: NULL value

Mladen, from the class kit:

There is no index entry corresponding to a row that = has all key colunmns that are NULL.

Bitmap indexes do store null entries in a way, since = they store 1's and 0's - yes and no for all the values in the = column.

So it seems to me, saying IS NULL or IS NOT NULL in a = query does not use an index to retrieve the data, it becomes a full = table scan, because it has nothing to work with.

Am I wrong?

Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319

V: 954.484.3191, x174
F: 954.484.2933
C: 954.658.5849
http://www.qode.com

"The information contained herein does not = express the opinion or position of Qode.com and cannot be attributed to = or made binding upon Qode.com."

-----Original Message-----
From: Gogala, Mladen [mailto:MGogala@oxhp.com]
Sent: Tuesday, October 10, 2000 12:05 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: NULL value

It's stored in the column because NULL !=3D NULL. The = only operators that
can be applied to the NULL value are IS and IS NOT. = Nothing else.

-----Original Message-----
Sent: Tuesday, October 10, 2000 7:26 AM
To: Multiple recipients of list ORACLE-L

Hi,
I have a logical question, any one who can make it = clear.
        create = table a (x number(2) unique);
        insert = into a values (null);
        1 row = created.
        insert = into a values (null);
        1 row = created.
Q:- I have a table 'A' have one field 'X' with = unique constraint, so a
unique index will be created for it. I inserted null = into this field two
times (or more then it).

If null is a value, Why these null stored into table = which have unique
constraint?
If null is not a VALUE then what it is?
Is there some special character for NULL?
If null is not a value why we have operators for = null?

Regards

Muhammad Awais
System Developer
Paktel Ltd. Pakistan
Mob. 0303 7757077
(92-51) 111-222-111-282

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: awaisb@paktel.com

Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051
San Diego, = California -- Public Internet = access / Mailing Lists
---------------------------------------------------------------= -----
To REMOVE yourself from this mailing list, send an = E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of = 'ListGuru') and in
the message BODY, include a line containing: UNSUB = ORACLE-L
(or the name of mailing list you want to be removed = from). You may
also send the HELP command for other information = (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gogala, Mladen
INET: MGogala@oxhp.com