Re: Function-based index on DML-active tables

Amir -

We encountered a similar issue with a 3rd party application executing a statement with improper datatype in a bind variable. For example,

create table t( x varchar2(50) not null primary key, y int ) /

Application code:
  select y from t where x = :bindvar

And "bindvar" in the application is actually a numeric datatype. Since the incoming datatype of the bind does not match the table, the execution plan may suffer. For our problem, we knew the varchar2 column contained only numbers (very poor design by the 3rd party), and that the bind variable would (should) always be a number. Knowing this, we created a "fake" environment for a session, got the execution plan to where we needed it, then used optimizer plan stability to persist the plan. For the 3rd party app, we have a logon trigger to enable plan stability, so whenever the offensive SQL statement is issued, it will use the plan we persisted.

HTH Glenn

Perhaps this is the problem you're trying to solve?

On 4/6/06, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:
>
> I agree but this is an Oracle applications code and getting execute via OCI.
>
>
> ________________________________
> From: Bjørn Dörr Jensen [mailto:B.D.Jensen_at_gmx.net]
> Sent: Thursday, April 06, 2006 12:40 PM
> To: Hameed, Amir; Dennis Williams
>
> Cc: oracle-l_at_freelists.org
> Subject: Re: Function-based index on DML-active tables
>
>
>
> Hi!
> Wouldn't it be better to manipulate the sql instead of making an fbi, eg:
> replace select * from a where b=2 with select * from a where b='2'...?
> /Greetings
> Bjørn
>
> ----- Original Message -----
> From: Hameed, Amir
> To: Dennis Williams
> Cc: oracle-l_at_freelists.org
> Sent: Wednesday, April 05, 2006 10:23 PM
> Subject: RE: Function-based index on DML-active tables
>
>
> Dennis,
> It is a very simple function; to_char(<column_name>)
>
> Thanks ________________________________
> From: Dennis Williams [mailto:oracledba.williams_at_gmail.com]
> Sent: Wednesday, April 05, 2006 3:30 PM
> To: Hameed, Amir
> Cc: oracle-l_at_freelists.org
> Subject: Re: Function-based index on DML-active tables
>
>
>
>
> Amir,
>
> I think the answer depends on the complexity of your function. If it is
> simple, then there probably is little penalty. If you get carried away with
> a very complex function, well all bets are off.
>
> Dennis Williams
>

--
http://www.freelists.org/webpage/oracle-l