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 0926819612B0 for ; Tue, 9 Sep 2014 22:14:07 +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 22:14:06 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3A3BF2F87F; Tue, 9 Sep 2014 16:14:05 -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 mWofJ5lmXKPP; Tue, 9 Sep 2014 16:14:05 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 564752F84E; Tue, 9 Sep 2014 16:13:24 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 09 Sep 2014 16:12:43 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 02D1F2F824 for ; Tue, 9 Sep 2014 16:12:43 -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 OgvjPCPrEO+V for ; Tue, 9 Sep 2014 16:12:42 -0400 (EDT) Received: from na01-bn1-obe.outbound.protection.outlook.com (mail-bn1blp0186.outbound.protection.outlook.com [207.46.163.186]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 911212CCE6 for ; Tue, 9 Sep 2014 16:12:18 -0400 (EDT) Received: from BN1BFFO11FD028.protection.gbl (10.58.144.30) by BN1BFFO11HUB027.protection.gbl (10.58.144.174) with Microsoft SMTP Server (TLS) id 15.0.1019.14; Tue, 9 Sep 2014 20:12:15 +0000 Received: from S7041VA006.soa.soaad.com (208.177.105.34) by BN1BFFO11FD028.mail.protection.outlook.com (10.58.144.91) with Microsoft SMTP Server (TLS) id 15.0.1019.14 via Frontend Transport; Tue, 9 Sep 2014 20:12:15 +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 16:12:14 -0400 From: "McPeak, Matt" To: ORACLE-L Subject: Improving query with implicit type conversion Thread-Topic: Improving query with implicit type conversion Date: Tue, 9 Sep 2014 20:12:14 +0000 Message-ID: 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_D7864FA3E7830B428CB2A5A5301B63EE01BC84D665S7041VA005soa_" 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:(6009001)(438002)(164054003)(199003)(189002)(106476002)(104016003)(95666004)(85306004)(107886001)(106356001)(229853001)(106466001)(107046002)(110136001)(77982001)(18717965001)(80022001)(21056001)(55846006)(19300405004)(71186001)(20776003)(64706001)(81342001)(31966008)(74662001)(81542001)(99396002)(50986999)(575784001)(54356999)(46102001)(44976005)(84326002)(16236675004)(33656002)(87936001)(86362001)(2656002)(85852003)(19625215002)(77096002)(83322001)(92566001)(4396001)(74502001)(79102001)(90102001)(97736003)(92726001)(76482001)(6806004)(83072002)(512954002)(15975445006)(15202345003)(19580395003);DIR:OUT;SFP:;SCL:1;SRVR:BN1BFFO11HUB027;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: 56275 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_D7864FA3E7830B428CB2A5A5301B63EE01BC84D665S7041VA005soa_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi! I have a query in a 3rd party package that looks like this: SELECT devl_project_id FROM cz_devl_projects cdp WHERE deleted_flag =3D 0 AND SUBSTR (orig_sys_ref, INSTR (TRIM (orig_sys_ref), ':', -1, 1) + 1, 100) =3D :b1 The query is taking longer to run than I would like. I tried creating a function based index on SUBSTR(orig_sys_ref.... +1, 100)= (see above), but it looks like the calling code is using a numeric value f= or :b1, meaning Oracle is doing an implicit type conversion. So, I thought to create the FBI on TO_NUMBER(SUBSTR(orig_sys_ref... +1, 100= )), but it doesn't improve things. I'm curious: why would this query (below) use the index... SELECT devl_project_id FROM cz_devl_projects cdp WHERE deleted_flag =3D 0 AND to_Number(SUBSTR (orig_sys_ref, INSTR (TRIM (orig_sys_ref), ':', -1, 1) + 1, 100)) =3D to_number(:b1) ... and this query ... SELECT devl_project_id FROM cz_devl_projects cdp WHERE deleted_flag =3D 0 AND SUBSTR (orig_sys_ref, INSTR (TRIM (orig_sys_ref), ':', -1, 1) + 1, 100) =3D to_number(:b1) ...not use the index? I guess the simple answer is that the implicit type = conversion isn't just sticking a TO_NUMBER() around the expression. Is the= re any way to figure out the expression I'd need to index to get this to wo= rk? When I look at the predicate information in DBMS_XPLAN, I get this: SQL_ID 1xu0zvvngwqsh, child number 0 ------------------------------------- SELECT cdp.devl_project_id,ROWID FROM cz_devl_projects cdp WHERE deleted_flag =3D 0 AND SUBSTR (orig_sys_ref, INSTR (TRIM (orig_sys_ref), ':', -1, 1) + 1, 100) =3D 12345 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. Thanks, Matt --_000_D7864FA3E7830B428CB2A5A5301B63EE01BC84D665S7041VA005soa_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi!

 

I have a query in a 3rd party package that looks lik= e this:

 

SELECT devl_project_id

FROM  cz_devl_projects cdp

WHERE deleted_flag =3D 0

  &nbs= p;   AND SUBSTR (orig_sys_ref, INSTR (TRIM (orig_sys_ref),

   =             &nb= sp;            =            ':',

   =             &nb= sp;            =            -= 1,

   =             &nb= sp;            =            1)

   =             &nb= sp;            =     + 1, 100) =3D :b1

 

The query is taking longer to run than I would like.=

 

I tried creating a function based index on SUBSTR(or= ig_sys_ref…. +1, 100) (see above), but it looks like the calling = code is using a numeric value for :b1, meaning Oracle is doing an implicit = type conversion.

 

So, I thought to create the FBI on TO_NUMBER(SUBSTR(= orig_sys_ref… +1, 100)), but it doesn’t improve things.

 

I’m curious: why would this query (below) use = the index…

 

SELECT devl_project_id

FROM  cz_devl_projects cdp

WHERE deleted_flag =3D 0

  &nbs= p;   AND to_Number(SUBSTR (orig_sys_ref, INSTR (TRIM (orig_sys_ref),

   =             &nb= sp;            =            ':',

   =             &nb= sp;            =            -= 1,

   =             &nb= sp;            =            1)

   =             &nb= sp;            =     + 1, 100)) =3D to_number(:b1)<= /o:p>

 

 

… and this query …

 

SELECT devl_project_id

FROM  cz_devl_projects cdp

WHERE deleted_flag =3D 0

  &nbs= p;   AND SUBSTR (orig_sys_ref, INSTR (TRIM (orig_sys_ref),

   =             &nb= sp;            =            ':',

   =             &nb= sp;            =            -= 1,

   =             &nb= sp;            =            1)

   =             &nb= sp;            =     + 1, 100) =3D to_number(:b1)<= /o:p>

 

 

…not use the index?  I guess the simple a= nswer is that the implicit type conversion isn’t just sticking a TO_N= UMBER() around the expression.  Is there any way to figure out the exp= ression I’d need to index to get this to work?

 

When I look at the predicate information in DBMS_XPL= AN, I get this:

 

SQL_ID  1xu0zvvngwqsh, child number 0 &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;      

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

 SELECT cdp.devl_project_id,ROWID FROM cz_devl_= projects cdp  WHERE        &nb= sp;            =         

deleted_flag =3D 0     &nbs= p; AND SUBSTR (orig_sys_ref, INSTR (TRIM      = ;            &n= bsp;            = ;    

(orig_sys_ref),      &= nbsp;           &nbs= p;            &= nbsp;        ':',    = ;             &= nbsp;           &nbs= p;       

        &nbs= p;            &= nbsp;     -1,      &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;        

1)        &n= bsp;            = ;            + 1= , 100) =3D 12345          = ;            &n= bsp;            = ;        

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            <= o:p>

Plan hash value: 379709735    &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;            = ;  

        &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            <= o:p>

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

| 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;            = ;           

   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;            = ;           

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

 

Thanks,

Matt

 

 

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