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...
Thanks,
Matt
From: Ryan January [mailto:rjanuary@xxxxxxxxx]
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@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 -
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.
Other related posts:
