RE: Improving query with implicit type conversion
Date: Tue, 9 Sep 2014 21:03:52 +0000
Message-ID: <D7864FA3E7830B428CB2A5A5301B63EE01BC84D6DE_at_S7041VA005.soa.soaad.com>
I don't think a hit would work - Oracle doesn't think the FBI matches the predicate. (In fact, if I run a hinted version in SQL*Navigator, it still does 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_at_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 creating 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. directly casting to a number on either side is allowing 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_at_subaru.com<mailto:vxsmimmcp_at_subaru.com>> 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))=12345 AND TO_NUMBER("DELETED_FLAG")=0)) So, I don't know what else to try except for TO_NUMBER(). Any thoughts? I cannot change the query syntax.
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 09 2014 - 23:03:52 CEST