Re: RE: Improving query with implicit type conversion

From: Fergal Taheny <ftaheny_at_gmail.com>
Date: Tue, 9 Sep 2014 23:06:05 +0100
Message-ID: <CAOuMUT4NVyfh3cbzMSpKcLP-Ayzgo-sCYcY1+Eh97h_RMOW2Yw_at_mail.gmail.com>



You could use DBMS_ADVANCED_REWRITE to add the to_number. Not the most elegant solution ever but should work.

Regards,
Fergal
On 9 Sep 2014 22:05, "McPeak, Matt" <vxsmimmcp_at_subaru.com> wrote:

> 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> 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 Wed Sep 10 2014 - 00:06:05 CEST

Original text of this message