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@xxxxxxxxx> 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@xxxxxxx]
> Sent: Thursday, April 06, 2006 12:40 PM
> To: Hameed, Amir; Dennis Williams
>
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx
> 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@xxxxxxxxx]
> Sent: Wednesday, April 05, 2006 3:30 PM
> To: Hameed, Amir
> Cc: oracle-l@xxxxxxxxxxxxx
> 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


Other related posts: