RE: Improving query with implicit type conversion

  • From: "McPeak, Matt" <vxsmimmcp@xxxxxxxxxx>
  • To: Ryan January <rjanuary@xxxxxxxxx>
  • Date: Tue, 9 Sep 2014 21:03:52 +0000

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...


From: Ryan January [mailto:rjanuary@xxxxxxxxx]
Sent: Tuesday, September 09, 2014 4:49 PM
To: McPeak, Matt
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.

On Sep 9, 2014, at 3:12 PM, McPeak, Matt 
<vxsmimmcp@xxxxxxxxxx<mailto:vxsmimmcp@xxxxxxxxxx>> 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 - 
              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.

Other related posts: