From oracle-l-bounce@freelists.org Fri Sep 9 13:18:56 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j89IIuRN021373 for ; Fri, 9 Sep 2005 13:18:56 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j89IIgIP021345 for ; Fri, 9 Sep 2005 13:18:42 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8FCC21EB152; Fri, 9 Sep 2005 13:18:28 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16111-07; Fri, 9 Sep 2005 13:18:28 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 07B901E9835; Fri, 9 Sep 2005 13:18:27 -0500 (EST) Message-ID: <5B257A26B4845C469B87871B6CEFE5070331AD58@usnjc04wmx003.tdwaterhouse.com> From: JayMiller@TDWaterhouse.com To: oracle-l@freelists.org Subject: Query started getting 1722 error - interesting explanation why Date: Fri, 9 Sep 2005 14:16:07 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5B56A.39FFE429" X-archive-position: 25238 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: JayMiller@TDWaterhouse.com Precedence: normal Reply-To: JayMiller@TDWaterhouse.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.1 required=5.0 tests=AWL,BAYES_00,HTML_50_60, HTML_FONTCOLOR_UNKNOWN,HTML_MESSAGE,NO_REAL_NAME autolearn=no version=2.63 ------_=_NextPart_001_01C5B56A.39FFE429 Content-Type: text/plain I got a call from a developer the other week complaining that a query in QA had started giving an ora-1722. It had worked fine for several months and the identical query was working fine in production. Select count(1) from prod.job_profile jp where step_id < 9999 and not exists (select 'x' from prod.job_log jl where jl.job_id = jp.job_id and ( jl.status in ('C', 'EI') ) and jl.batch_id in (select to_number(text_value) from prod.batch_parameter where name = 'BATCH_ID')); Investigating confirmed this, along with the fact that the query was comparing a string (jl.batch_id) with a number (to_number(text_value)). But why did it suddenly stop working and why did it still work in production? Comparing explain plans between QA and production I saw that they were different (statistics had changed during QA testing). The query that failed was using an index on jl.batch_id. Apparently Oracle automatically took care of converting the string to a number *unless* the number was being used to access an index on the varchar2 column. Interesting behavior. I can see why it might work that way but it's certainly not intuitive. Of course explaining this to the developer took quite a while. "It worked before, why shouldn't it work now? What's wrong with Oracle?" Oracle 9.2.0.6 Jay Miller Sr. Oracle DBA . ----------------------------------------- This message is confidential and sent by TD Waterhouse solely for use by the intended recipient. If you are not the intended recipient, you are hereby notified that any use, distribution or copying of this communication is strictly prohibited. This should not be deemed as an offer or solicitation, to buy or sell any product. Any 3rd party information contained herein was prepared by sources deemed reliable, but is not guaranteed. TD Waterhouse does not accept electronic instructions that would require an original signature. Information received by or sent from TD Waterhouse is stored, subject to review, and may be produced to regulatory authorities or others with a legal right to such. ------_=_NextPart_001_01C5B56A.39FFE429 Content-Type: text/html Content-Transfer-Encoding: quoted-printable =0D=0A=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A<= style>=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A

I got a call from a developer the other=0D=0Aweek complaining t= hat a query in QA had started giving an ora-1722=2E  It had=0D=0Aworke= d fine for several months and the identical query was working fine in=0D=0A= production=2E

=0D=0A=0D=0A

 

=0D=0A=0D=0A

Select count(1) =

=0D=0A=0D=0A

fr= om prod=2Ejob_profile jp

=0D=0A=0D=0A

= where step_id < 9999<= /p>=0D=0A=0D=0A

and= not exists (select 'x' from prod=2Ejob_log=0D=0Ajl

=0D=0A= =0D=0A

  =             &nb= sp; where jl=2Ejob_id =3D jp=2Ejob_id

=0D=0A=0D=0A

    &n= bsp;           and ( jl= =2Estatus in ('C',=0D=0A'EI') )

=0D=0A=0D=0A

     =            and  jl= =2Ebatch_id in=0D=0A(select to_number(text_value)

=0D=0A= =0D=0A

  =             &nb= sp;            =          from prod=2Ebatch_pa= rameter

=0D=0A=0D=0A

         &n= bsp;            = ;            &n= bsp;  where=0D=0Aname =3D 'BATCH_ID'));

=0D=0A=0D=0A<= p class=3DMsoNormal> 

=0D=0A=0D=0A

I= nvestigating confirmed this, along with=0D=0Athe fact that the query was co= mparing a string (jl=2Ebatch_id) with a number (to_number(text_value))=2E&n= bsp;=0D=0ABut why did it suddenly stop working and why did it still work in= production?

=0D=0A=0D=0A

 

=0D=0A=0D=0A

Comparing explain plans betwee= n QA and=0D=0Aproduction I saw that they were different (statistics had cha= nged during QA=0D=0Atesting)=2E  The query that failed was using an in= dex on jl=2Ebatch_id=2E 

=0D=0A=0D=0A

 

=0D=0A=0D= =0A

Apparently Orac= le automatically took care=0D=0Aof converting the string to a number *unles= s* the number was being used to=0D=0Aaccess an index on the varchar2 column= =2E  Interesting behavior=2E  I can see why=0D=0Ait might work th= at way but it's certainly not intuitive=2E

=0D=0A=0D=0A =

=0D=0A=0D=0A

Of= course explaining this to the developer=0D=0Atook quite a while=2E  &= quot;It worked before, why shouldn't it work now?  What's=0D=0Awrong w= ith Oracle?"

=0D=0A=0D=0A

 

=0D=0A=0D=0A

Oracle 9=2E2=2E0=2E6

=0D=0A=0D=0A

 

=0D=0A=0D=0A

 

=0D=0A=0D=0A
=0D=0A= =0D=0A

Jay Miller<= /span>

=0D=0A=0D=0A

Sr=2E Oracle DBA

=0D=0A=0D=0A
=0D=0A=0D= =0A

 

=0D=0A=0D=0A
=0D=0A=0D=0A

=2E

=0D=0A=0D=0A
=0D=0A=0D=0A= =0D=0A=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A=0D=0A

=0D=0A

=0D=0AThis message is confidential and sent by TD Waterho= use solely for use
by the intended recipient=2E If you are not the inte= nded recipient, you
are hereby notified that any use, distribution or co= pying of this
communication is strictly prohibited=2E This should not b= e deemed as an
offer or solicitation, to buy or sell any product=2E Any = 3rd party
information contained herein was prepared by sources deemed re= liable,
but is not guaranteed=2E TD Waterhouse does not accept electron= ic
instructions that would require an original signature=2E Information<= br>received by or sent from TD Waterhouse is stored, subject to review,
= and may be produced to regulatory authorities or others with a legal
rig= ht to such=2E=0D=0A

=0D=0A=0D=0A=0D=0A ------_=_NextPart_001_01C5B56A.39FFE429-- -- http://www.freelists.org/webpage/oracle-l