Re: Improving query with implicit type conversion
- From: Ryan January <rjanuary@xxxxxxxxx>
- To: vxsmimmcp@xxxxxxxxxx
- Date: Tue, 9 Sep 2014 15:48:32 -0500
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> 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:
