Re: Improving query with implicit type conversion

From: Ryan January <rjanuary_at_gmail.com>
Date: Tue, 9 Sep 2014 15:48:32 -0500
Message-Id: <6E459EA9-9222-4049-B728-A5FB44025187_at_gmail.com>

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> 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 - 22:48:32 CEST