RE: Go NULL values into an index?

From: Alex Hillman <alex_hillman_at_physia.com>
Date: Tue, 9 Jan 2001 12:27:15 -0500
Message-Id: <10736.126176@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_01C07A61.6CBCC4A0

Content-Type: text/plain;

charset="iso-8859-1"

I did not try it but this should work. Disable not null constraint, put null into the column and then enable novalidate not null constraint.

Also the only case when nuuls can be in the index if the nullable column is one of several columns on which index defined. For example if index defined on column c1 null values of c1 will not go to the index. However if index defined on (c1,c2,c3) - any combination of these columns which has at least one of columns values not null will go into the index.

Alex Hillman

-----Original Message-----

From: Jeffery Stevenson [mailto:jeff_at_mpv.com] Sent: Tuesday, January 09, 2001 10:32 AM To: Multiple recipients of list ORACLE-L Subject: RE: Go NULL values into an index?

Now why would anyone want to know a useless trick like that? :)

Besides, it would require that you have permissions to alter the constraints on the table...

Jeff

-----Original Message-----

Sent: Tuesday, January 09, 2001 12:11 AM To: Multiple recipients of list ORACLE-L

Hi Jeffery

u meant to say that there is some way to put null values in not null column.

Would u tell me how to do that.

Thanks & Regards

Ram
Tel:4236953

Message History

Please respond to ORACLE-L_at_fatcity.com

To: ORACLE-L_at_fatcity.com
cc:

AHA! It's a trick question...you shouldn't be able to put null values in a not null field--not to say that it can't be done, but that is beyond the scope of this question. :) Just so we get this right, it is supposed to be a nullable column, right?

Jeffery Stevenson
Chief Databeast Tamer, late at night with a tummy full of Coronas! Medical Present Value, Inc.
Austin, TX

-----Original Message-----

[mailto:Andreas.Haunschmidt_at_voest.co.at] Sent: Monday, January 08, 2001 9:50 PM
To: Multiple recipients of list ORACLE-L

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).
--

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

Author: Jeffery Stevenson
INET: jeff_at_mpv.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

--

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

Author:
INET: ramakrishna.gv_at_db.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

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

------_=_NextPart_001_01C07A61.6CBCC4A0

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.2448.0">

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

I did not try it but this should work. Disable not = null constraint, put null into the column and then enable novalidate = not null constraint.

Also the only case when nuuls can be in the index if = the nullable column is one of several columns on which index defined. = For example if index defined on column c1 null values of c1 will not go = to the index. However if index defined on (c1,c2,c3) - any combination = of these columns which has at least one of columns values not null will = go into the index.

Alex Hillman

-----Original Message----- From: Jeffery Stevenson [<A = HREF=3D"mailto:jeff_at_mpv.com">mailto:jeff_at_mpv.com</A>]

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

Now why would anyone want to know a useless trick = like that?   :)

Besides, it would require that you have permissions = to alter the constraints
 on the table...

Jeff

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>Sent: Tuesday, January 09, 2001 12:11 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
</P>
<BR>

Hi Jeffery
    u meant to say that there is some way = to put null values in not null

<BR><FONT SIZE=3D2>column.</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; Would u tell me how to do that.</FONT>
</P>

<P><FONT SIZE=3D2>&nbsp;Thanks &amp; Regards</FONT>
<BR><FONT SIZE=3D2>&nbsp;----------------------------</FONT>
<BR><FONT SIZE=3D2>&nbsp;Ram</FONT>
<BR><FONT SIZE=3D2>&nbsp;Tel:4236953</FONT>

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

History

<BR><FONT SIZE=3D2>----------------------------------------</FONT>
</P>

Please respond to ORACLE-L_at_fatcity.com

<P><FONT SIZE=3D2>To:&nbsp;&nbsp; ORACLE-L_at_fatcity.com</FONT>
<BR><FONT SIZE=3D2>cc:</FONT>
</P>
<BR>
<BR>

AHA!  It's a trick question...you shouldn't be = able to put null values in a
 not null field--not to say that it can't be done, = but that is beyond the
 scope of this question.  :)  Just so we = get this right, it is supposed to be a nullable column, right?

Jeffery Stevenson Chief Databeast Tamer, late at night with a tummy = full of Coronas!

<BR><FONT SIZE=3D2>Medical Present Value, Inc.</FONT>
<BR><FONT SIZE=3D2>Austin, TX</FONT>
</P>

-----Original Message----- [<A =
HREF=3D"mailto:Andreas.Haunschmidt_at_voest.co.at">mailto:Andreas.Haunschmi= dt_at_voest.co.at</A>]

<BR><FONT SIZE=3D2>Sent: Monday, January 08, 2001 9:50 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</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:

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

<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 = (like subscribing).
 --
 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: Jeffery Stevenson</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: jeff_at_mpv.com</FONT>
</P>

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

</P>
<BR>
<BR>
<BR>

-- 
 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: </FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: ramakrishna.gv_at_db.com</FONT>
</P>

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 = (like subscribing).
 -- 
 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: Jeffery Stevenson</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: jeff_at_mpv.com</FONT>
</P>

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