Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 36E8619612E1 for ; Tue, 9 Sep 2014 23:05:47 +0200 (CEST) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTP for ; Tue, 9 Sep 2014 23:05:47 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 873842F8D9; Tue, 9 Sep 2014 17:05:45 -0400 (EDT) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id DxRCIw3JLnny; Tue, 9 Sep 2014 17:05:45 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A95182F8A7; Tue, 9 Sep 2014 17:05:04 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 09 Sep 2014 17:04:23 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 75BA52F85D for ; Tue, 9 Sep 2014 17:04:23 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id CHZ4ULj3xIDL for ; Tue, 9 Sep 2014 17:04:23 -0400 (EDT) Received: from na01-bl2-obe.outbound.protection.outlook.com (mail-bl2lp0211.outbound.protection.outlook.com [207.46.163.211]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3AF692F85B for ; Tue, 9 Sep 2014 17:03:59 -0400 (EDT) Received: from BN1BFFO11FD058.protection.gbl (10.58.144.33) by BN1BFFO11HUB021.protection.gbl (10.58.144.168) with Microsoft SMTP Server (TLS) id 15.0.1019.14; Tue, 9 Sep 2014 21:03:56 +0000 Received: from S7041VA006.soa.soaad.com (208.177.105.34) by BN1BFFO11FD058.mail.protection.outlook.com (10.58.145.13) with Microsoft SMTP Server (TLS) id 15.0.1019.14 via Frontend Transport; Tue, 9 Sep 2014 21:03:56 +0000 Received: from S7041VA005.soa.soaad.com ([fe80::c4f5:a627:8465:d9e]) by S7041VA006.soa.soaad.com ([fe80::34a8:9318:3c53:f68%16]) with mapi id 14.02.0318.001; Tue, 9 Sep 2014 17:03:52 -0400 From: "McPeak, Matt" To: Ryan January CC: ORACLE-L Subject: RE: Improving query with implicit type conversion Thread-Topic: Improving query with implicit type conversion Date: Tue, 9 Sep 2014 21:03:52 +0000 Message-ID: References: <6E459EA9-9222-4049-B728-A5FB44025187@gmail.com> In-Reply-To: <6E459EA9-9222-4049-B728-A5FB44025187@gmail.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [172.29.213.7] Content-Type: multipart/alternative; boundary="_000_D7864FA3E7830B428CB2A5A5301B63EE01BC84D6DES7041VA005soa_" MIME-Version: 1.0 X-EOPAttributedMessage: 0 X-Forefront-Antispam-Report: CIP:208.177.105.34;CTRY:US;IPV:NLI;EFV:NLI;SFV:NSPM;SFS:(438002)(24454002)(377454003)(164054003)(199003)(189002)(95666004)(55846006)(20776003)(97736003)(90102001)(85306004)(107046002)(106476002)(110136001)(64706001)(106466001)(80022001)(104016003)(19300405004)(79102001)(19617315012)(77982001)(21056001)(4396001)(81342001)(18717965001)(106356001)(19625215002)(74502001)(74662001)(81542001)(54356999)(50986999)(76176999)(33656002)(46102001)(99396002)(6806004)(16236675004)(92566001)(87936001)(1411001)(83322001)(19580405001)(2656002)(15975445006)(512954002)(19580395003)(85852003)(44976005)(77096002)(71186001)(31966008)(76482001)(84326002)(15202345003)(92726001)(86362001)(83072002);DIR:OUT;SFP:;SCL:1;SRVR:BN1BFFO11HUB021;H:S7041VA006.soa.soaad.com;FPR:;MLV:sfv;PTR:InfoNoRecords;A:1;MX:1;LANG:en; X-Microsoft-Antispam: BCL:0;PCL:0;RULEID:;UriScan:; X-Forefront-PRVS: 0329B15C8A Received-SPF: Pass (protection.outlook.com: domain of subaru.com designates 208.177.105.34 as permitted sender) receiver=protection.outlook.com; client-ip=208.177.105.34; helo=S7041VA006.soa.soaad.com; Authentication-Results: spf=pass (sender IP is 208.177.105.34) smtp.mailfrom=vxsmimmcp@subaru.com; X-OriginatorOrg: subaru.com X-archive-position: 56280 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: vxsmimmcp@subaru.com Precedence: normal Reply-To: vxsmimmcp@subaru.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --_000_D7864FA3E7830B428CB2A5A5301B63EE01BC84D6DES7041VA005soa_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I don't think a hit would work - Oracle doesn't think the FBI matches the p= redicate. (In fact, if I run a hinted version in SQL*Navigator, it still d= oes not use the index). But I think I could use similar methods to change = to or add a predicate that would use my index. I just though it should be = easier than that... Thanks, Matt From: Ryan January [mailto:rjanuary@gmail.com] Sent: Tuesday, September 09, 2014 4:49 PM To: McPeak, Matt Cc: ORACLE-L Subject: Re: Improving query with implicit type conversion I apologize for missing this before. It's always been my experience that c= reating an index based upon predicates from query plans works as expected. To directly answer your question, even though orig_sys_ref may start of as= a number, you do end up with a string after your string functions. direct= ly casting to a number on either side is allowing the index usage. The oth= er option you have is to somehow supply the bind as character data. Thinking about the larger problem... if the sql does not change, is sqlpatc= h an option? You may have a quick look at the following page for an exampl= e. https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a On Sep 9, 2014, at 3:12 PM, McPeak, Matt > wrote: Plan hash value: 379709735 ---------------------------------------------------------------------------= --------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | = A-Time | Buffers | ---------------------------------------------------------------------------= --------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00= :00:00.29 | 6809 | |* 1 | TABLE ACCESS FULL| CZ_DEVL_PROJECTS | 1 | 21 | 0 |00= :00:00.29 | 6809 | ---------------------------------------------------------------------------= --------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter((TO_NUMBER(SUBSTR("ORIG_SYS_REF",INSTR(TRIM("ORIG_SYS_REF"),'= :',-1,1)+1,10 0))=3D12345 AND TO_NUMBER("DELETED_FLAG")=3D0)) So, I don't know what else to try except for TO_NUMBER(). Any thoughts? I= cannot change the query syntax. --_000_D7864FA3E7830B428CB2A5A5301B63EE01BC84D6DES7041VA005soa_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I don’t think a hit= would work – Oracle doesn’t think the FBI matches the predicat= e.  (In fact, if I run a hinted version in SQL*Navigator, it still doe= s not use the index).  But I think I could use similar methods to change to= or add a predicate that would use my index.  I just though it should = be easier than that…

 <= /p>

Thanks,=

Matt

 <= /p>

 <= /p>

From: Ryan Jan= uary [mailto:rjanuary@gmail.com]
Sent: Tuesday, September 09, 2014 4:49 PM
To: McPeak, Matt
Cc: ORACLE-L
Subject: Re: Improving query with implicit type conversion

 

I apologize for missing this before.  It's alwa= ys been my experience that creating an index based upon predicates from que= ry plans works as expected.  

 

To directly answer your question,  even though = orig_sys_ref may start of as a number, you do end up with a string after yo= ur string functions.  directly casting to a number on either side is a= llowing the index usage.  The other option you have is to somehow supply the bind as character data.

 

Thinking about the larger problem... if the sql does= not change, is sqlpatch an option?  You may have a quick look at the = following page for an example. https://blogs.oracle.com/optimizer/entry/how= _can_i_hint_a

 

 

On Sep 9, 2014, at 3:12 PM, McPeak, Matt <vxsmimmcp@subaru.com> wrote:



Plan hash value: 379709735  &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;    

      &nb= sp;            =             &nb= sp;            =             &nb= sp;            =             &nb= sp;            =   

-----------------------------------------------------------= -------------------------------------=

| Id  | Operation      &= nbsp;  | Name         &nb= sp;   | Starts | E-Rows | A-Rows |   A-Time  = | Buffers |

-----------------------------------------------------------= -------------------------------------=

|   0 | SELECT STATEMENT  |   = ;            &n= bsp;  |      1 |     = ;   |      0 |00:00:00.29 |  &= nbsp; 6809 |

|*  1 |  TABLE ACCESS FULL| CZ_DEVL_PROJECTS |&nb= sp;     1 |     21 |  &nb= sp;   0 |00:00:00.29 |    6809 |

-----------------------------------------------------------= -------------------------------------=

          = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;            = ;           

Predicate Information (identified by operation id): &n= bsp;            = ;            &n= bsp;            = ;     

--------------------------------------------------- &n= bsp;            = ;            &n= bsp;            = ;     

          = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;            = ;           <= span style=3D"font-size:11.0pt;font-family:"Calibri","sans-s= erif"">

   1 - filter((= TO_NUMBER(SUBSTR("ORIG_SYS_REF",INSTR(TRIM("ORIG_SYS_REF&quo= t;),':',-1,1)+1,10       =

       &nbs= p;      0))=3D12345 AND TO_NUMBER("D= ELETED_FLAG")=3D0))        &nb= sp;            =             &nb= sp;    

          = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;            = ;           <= span style=3D"font-size:11.0pt;font-family:"Calibri","sans-s= erif"">

So, I don’t know what else to try= except for TO_NUMBER().  Any thoughts?  I cannot change the quer= y syntax.

 

--_000_D7864FA3E7830B428CB2A5A5301B63EE01BC84D6DES7041VA005soa_-- -- http://www.freelists.org/webpage/oracle-l