RE: Go NULL values into an index?

From: Amar Kumar Padhi <TS2017_at_emirates.com>
Date: Tue, 9 Jan 2001 08:36:08 +0400
Message-Id: <10735.126075@fatcity.com>

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_01C079F5.AFB88DB0
Content-Type: text/plain;

charset="iso-8859-1"

Hi,
If it is a not null column, you cannot store null now. If you are storing null values in the column on which an index is defined, the index will ignore the records with such instances. So checking for nulls in your query will not use an index.

Thanks,
Amar Kumar Padhi,
Software Consultant

-----Original Message-----
From: Haunschmidt Andreas VASL/FAS
[mailto:Andreas.Haunschmidt_at_voest.co.at] Sent: Tuesday, January 09, 2001 7:50 AM
To: Multiple recipients of list ORACLE-L Subject: Go NULL values into an index?

Hi Board Members!
Maybe a often posed question,
but I dare to ask you directly:

If there is a not null column named 'c1' and this column is part of an index

eg.: create (unique) index idx1 on t1(c1,c2...) - will the null values "go" into the index, so that a select * from t1 where c1 is null; or any other select statement can make use of the index ? Thanks in advance!

Andreas
Andreas
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Haunschmidt Andreas VASL/FAS
INET: Andreas.Haunschmidt_at_voest.co.at

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_at_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_01C079F5.AFB88DB0
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2652.35">

<TITLE>RE: Go NULL values into an index?</TITLE>
</HEAD>
<BODY>

Hi,

<BR><FONT SIZE=3D2>If it is a not null column, you cannot store null =
now.</FONT>
<BR><FONT SIZE=3D2>If you are storing null values in the column on =

which an index is defined, the index will ignore the records with such = instances. So checking for nulls in your query will not use an = index.

<P><FONT SIZE=3D2>Thanks,</FONT>
<BR><FONT SIZE=3D2>Amar Kumar Padhi,</FONT>
<BR><FONT SIZE=3D2>Software Consultant</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>

From: Haunschmidt Andreas VASL/FAS [<A =
HREF=3D"mailto:Andreas.Haunschmidt_at_voest.co.at">mailto:Andreas.Haunschmi= dt_at_voest.co.at</A>]

<BR><FONT SIZE=3D2>Sent: Tuesday, January 09, 2001 7:50 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Go NULL values into an index?</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>Hi Board Members!</FONT>
<BR><FONT SIZE=3D2>&nbsp;Maybe a often posed question,</FONT>

but I dare to ask you directly:

<P><FONT SIZE=3D2>&nbsp;If there is a not null column named 'c1' and =
</FONT>
<BR><FONT SIZE=3D2>&nbsp;this column is part of an index </FONT>
<BR><FONT SIZE=3D2>&nbsp;- eg.: create (unique) index idx1 on =

t1(c1,c2...) -
  will the null values "go" into the = index, so that a

<BR><FONT SIZE=3D2>&nbsp;select * from t1 where c1 is null;</FONT>
<BR><FONT SIZE=3D2>&nbsp;or any other select statement </FONT>
<BR><FONT SIZE=3D2>&nbsp;can make use of the index ?</FONT>
<BR><FONT SIZE=3D2>&nbsp;Thanks in advance!</FONT>
</P>

<P><FONT SIZE=3D2>&nbsp;Andreas</FONT>
<BR><FONT SIZE=3D2>&nbsp; Andreas</FONT>

-- 
 Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A>

<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Haunschmidt Andreas VASL/FAS</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: Andreas.Haunschmidt_at_voest.co.at</FONT>
</P>

Fat City Network Services    -- (858) = 538-5051  FAX: (858) 538-5051 San Diego, =
California        -- Public Internet = access / Mailing Lists
 <FONT =

SIZE=3D2>---------------------------------------------------------------=

-----
 To REMOVE yourself from this mailing list, send an = E-Mail message
 to: ListGuru_at_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 = Received on Mon Jan 08 2001 - 22:36:08 CST