| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: select with Null
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_01C054B8.9354A4CC
Content-Type: text/plain;
charset="iso-8859-1"
The NVL() will disable the index. See below.
SQL> desc b_list
Name
ID NOT NULL USER_ID NOT NULL NAME NOT NULL EXPDATE
SQL> SELECT INDEX_NAME, COLUMN_NAME FROM USER_IND_COLUMNS 2 WHERE TABLE_NAME = 'B_LIST';
INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
BL_IDX EXPDATE
1 select /*+ index (bin_list bl_idx) */
2 count(*) from b_list
3* where expdate > sysdate - 10
SQL> /
COUNT(*)
999
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9) 1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'BL_IDX' (NON-UNIQUE) (Cost=2 Card
=70 Bytes=630)
1 select /*+ index (bin_list bl_idx) */
2 count(*) from b_list
3* where nvl(expdate,sysdate ) > sysdate - 10
SQL> /
COUNT(*)
3062
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=9) 1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'BIN_LIST' (Cost=3 Card=70 Bytes=
630)
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
"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: Marcos.Vera_at_msconsultores.com.pe
[mailto:Marcos.Vera_at_msconsultores.com.pe]
Sent: Wednesday, November 22, 2000 1:16 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: select with Null
select d.discount_type
from discount d
where (NVL(d.company_code,'XX') = 'XX')
and (NVL(d.operator_code,'YY') = 'YY')
Raj Gopalan <raj.gopalan_at_netdecisions.co.uk> on 22/11/2000 12:35:24 PM
Please respond to ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
cc: (bcc: Marcos Vera/M&S Consultores/51)
Subject: select with Null
Hello list,
I have a query
select d.discount_type
from discount d
where (d.company_code = 'XX' or
d.company_code is null)
and (d.operator_code = 'YY' or
d.operator_code is null)
Apparently, oracle is not using the index since I am using IS NULL. But the functional requirement is such that the query need to consider records even if company_code, Operator_code ,... is null.
Any thoughts on how do I modify this query so that the index is being used.
TIA Raj
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raj Gopalan INET: raj.gopalan_at_netdecisions.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing ListsReceived on Wed Nov 22 2000 - 13:15:22 CST
--------------------------------------------------------------------
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: INET: Marcos.Vera_at_msconsultores.com.pe 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_01C054B8.9354A4CC
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.2650.12"> <TITLE>RE: select with Null</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>The NVL() will disable the index. See = below.</FONT> </P> <P><FONT SIZE=3D2>SQL> desc b_list</FONT> <BR><FONT = SIZE=3D2> Name  = ;  = ; </FONT> <BR><FONT SIZE=3D2> -----------------------------</FONT> <BR><FONT = SIZE=3D2> ID &= nbsp; NOT = NULL = </FONT> <BR><FONT = SIZE=3D2> USER_ID &n= bsp; NOT NULL</FONT> <BR><FONT = SIZE=3D2> NAME  = ; NOT = NULL = </FONT> <BR><FONT = SIZE=3D2> EXPDATE &n= bsp; &n= bsp; </FONT> </P> <P><FONT SIZE=3D2>SQL> SELECT INDEX_NAME, COLUMN_NAME FROM = USER_IND_COLUMNS</FONT> <BR><FONT SIZE=3D2> 2 WHERE TABLE_NAME =3D = 'B_LIST';</FONT> </P> <P><FONT = SIZE=3D2>INDEX_NAME  = ; = COLUMN_NAME</FONT> <BR><FONT SIZE=3D2>------------------------------ =
------------------------------</FONT>
<BR><FONT = SIZE=3D2>BL_IDX &nb= sp; &nb= sp; EXPDATE</FONT> </P> <BR> <P><FONT SIZE=3D2> 1 select /*+ index (bin_list bl_idx) = */</FONT> <BR><FONT SIZE=3D2> 2 count(*) from b_list</FONT> <BR><FONT SIZE=3D2> 3* where expdate > sysdate - 10</FONT> <BR><FONT SIZE=3D2>SQL> /</FONT> </P> <P><FONT SIZE=3D2> COUNT(*)</FONT> <BR><FONT SIZE=3D2>----------</FONT> <BR><FONT SIZE=3D2> 999</FONT> </P> <BR> <P><FONT SIZE=3D2>Execution Plan</FONT> <BR><FONT = SIZE=3D2>----------------------------------------------------------</FON= T> <BR><FONT SIZE=3D2> 0 SELECT = STATEMENT Optimizer=3DCHOOSE (Cost=3D2 Card=3D1 Bytes=3D9)</FONT> <BR><FONT SIZE=3D2> 1 0 SORT = (AGGREGATE)</FONT> <BR><FONT SIZE=3D2> 2 = 1 INDEX (RANGE SCAN) OF 'BL_IDX' (NON-UNIQUE) = (Cost=3D2 Card</FONT> <BR><FONT = SIZE=3D2> =3D70 = Bytes=3D630)</FONT> </P> <BR> <P><FONT SIZE=3D2> 1 select /*+ index (bin_list bl_idx) = */</FONT> <BR><FONT SIZE=3D2> 2 count(*) from b_list</FONT> <BR><FONT SIZE=3D2> 3* where nvl(expdate,sysdate ) > sysdate - = 10</FONT> <BR><FONT SIZE=3D2>SQL> /</FONT> </P> <P><FONT SIZE=3D2> COUNT(*)</FONT> <BR><FONT SIZE=3D2>----------</FONT> <BR><FONT SIZE=3D2> 3062</FONT> </P> <BR> <P><FONT SIZE=3D2>Execution Plan</FONT> <BR><FONT = SIZE=3D2>----------------------------------------------------------</FON= T> <BR><FONT SIZE=3D2> 0 SELECT = STATEMENT Optimizer=3DCHOOSE (Cost=3D3 Card=3D1 Bytes=3D9)</FONT> <BR><FONT SIZE=3D2> 1 0 SORT = (AGGREGATE)</FONT> <BR><FONT SIZE=3D2> 2 = 1 TABLE ACCESS (FULL) OF 'BIN_LIST' (Cost=3D3 = Card=3D70 Bytes=3D</FONT> <BR><FONT = SIZE=3D2> = 630)</FONT> </P> <P><FONT SIZE=3D2>Lisa Rutland Koivu</FONT> <BR><FONT SIZE=3D2>Oracle Database Administrator</FONT> <BR><FONT SIZE=3D2>Qode.com</FONT> <BR><FONT SIZE=3D2>4850 North State Road 7</FONT> <BR><FONT SIZE=3D2>Suite G104</FONT> <BR><FONT SIZE=3D2>Fort Lauderdale, FL 33319</FONT> </P> <P><FONT SIZE=3D2>V: 954.484.3191, x174</FONT> <BR><FONT SIZE=3D2>F: 954.484.2933 </FONT> <BR><FONT SIZE=3D2>C: 954.658.5849</FONT> <BR><FONT SIZE=3D2><A HREF=3D"http://www.qode.com" = TARGET=3D"_blank">http://www.qode.com</A></FONT> </P> <P><FONT SIZE=3D2>"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."</FONT></P> <BR> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: Marcos.Vera_at_msconsultores.com.pe</FONT> <BR><FONT SIZE=3D2>[<A = HREF=3D"mailto:Marcos.Vera_at_msconsultores.com.pe">mailto:Marcos.Vera_at_msco= nsultores.com.pe</A>]</FONT> <BR><FONT SIZE=3D2>Sent: Wednesday, November 22, 2000 1:16 PM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: Re: select with Null</FONT> </P> <BR> <BR> <BR> <BR> <P><FONT SIZE=3D2>select d.discount_type</FONT> <BR><FONT SIZE=3D2> from discount d</FONT> <BR><FONT SIZE=3D2> where (NVL(d.company_code,'XX') =3D = 'XX')</FONT> <BR><FONT SIZE=3D2> and = (NVL(d.operator_code,'YY') =3D 'YY')</FONT> </P> <BR> <BR> <BR> <BR> <BR> <BR> <BR> <P><FONT SIZE=3D2>Raj Gopalan <raj.gopalan_at_netdecisions.co.uk> on = 22/11/2000 12:35:24 PM</FONT> </P> <P><FONT SIZE=3D2>Please respond to ORACLE-L_at_fatcity.com</FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT> </P> <BR> <P><FONT = SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT> <BR><FONT SIZE=3D2> To: Multiple = recipients of list = ORACLE-L &nbs= p; </FONT> <BR><FONT = SIZE=3D2> = <ORACLE-L_at_fatcity.com> &n= bsp; &n= bsp; </FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT> <BR><FONT SIZE=3D2> cc: (bcc: Marcos = Vera/M&S = Consultores/51) &nb= sp; </FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT> <BR><FONT SIZE=3D2> Subject: select with = Null &n= bsp; &n= bsp; = </FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; &nb= sp; &nb= sp; </FONT> </P> <BR> <BR> <BR> <BR> <BR> <BR> <P><FONT SIZE=3D2>Hello list,</FONT> </P> <P><FONT SIZE=3D2>I have a query</FONT> </P> <P><FONT SIZE=3D2>select d.discount_type</FONT> <BR><FONT SIZE=3D2> from discount d</FONT> <BR><FONT SIZE=3D2> where (d.company_code =3D 'XX' or</FONT> <BR><FONT = SIZE=3D2> &nb= sp; d.company_code is null)</FONT> <BR><FONT SIZE=3D2> and (d.operator_code =3D = 'YY' or</FONT> <BR><FONT = SIZE=3D2> &nb= sp; d.operator_code is null)</FONT> </P> <P><FONT SIZE=3D2>Apparently, oracle is not using the index since I am = using IS NULL. But the</FONT> <BR><FONT SIZE=3D2>functional requirement is such that the query need = to consider records even</FONT> <BR><FONT SIZE=3D2>if company_code, Operator_code ,... is null.</FONT> </P> <P><FONT SIZE=3D2>Any thoughts on how do I modify this query so that = the index is being used.</FONT> </P> <P><FONT SIZE=3D2>TIA</FONT> </P> <P><FONT SIZE=3D2>Raj</FONT> <BR><FONT SIZE=3D2>--</FONT> <BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT> <BR><FONT SIZE=3D2>--</FONT> <BR><FONT SIZE=3D2>Author: Raj Gopalan</FONT> <BR><FONT SIZE=3D2> INET: raj.gopalan_at_netdecisions.co.uk</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB = ORACLE-L</FONT> <BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed = from). You may</FONT> <BR><FONT SIZE=3D2>also send the HELP command for other information = (like subscribing).</FONT> </P> <BR> <BR> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: </FONT> <BR><FONT SIZE=3D2> INET: Marcos.Vera_at_msconsultores.com.pe</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
 |
 |