Re: Binding on a char column

hpuxrac ha scritto:

> silvia.fama wrote:
> > Oh, yes, now I understand better! Thanks!
> >
> > What I meant with my last message, was that I'd like not to use RTRIM.
> > And I was asking if oracle gives to user the possibility not to use
> > RTRIM specifying an option in oracle environment. I don't know if it's
> > clear my question.
>
> There's no way to tell oracle not to pay attention to RTRIM if it comes
> in from an application in a SQL statement. It parses and optimizes SQL
> based on what is sent in to it.
>
>
> >
> > I understand about indexes, char vs. varchar, but for me these are big
> > changes to introduce to my database.
> > Till now we didn't use bind variables and we could execute an sql
> > queries such:
> > select * from table where column = 'test' also if column is char(10).
> > Now with bind variables 'test' is binded.
> >
> > I was searching a non expensive way to implement it. Indexes or migrate
> > to varchar are just a little problematic fo me. If oracle manages this
> > behaviour (char column with bind variable) could be very useful for me!
> > I don't know if you could understand me!
>
> As I noted earlier, you may be able to get to benefit from adding a
> function based index to tables where the SQL is currently being
> generated with an RTRIM ...
>
> create index my_new_index on my_existing_table(RTRIM(my_char_column))
> tablespace my_tablespace /* other parameters */;
>
> Then use dbms_stats to get statistics on the index.
>
> Now SQL that includes an RTRIM(my_char_column) gets a re-visit by the
> optimizer and perhaps a better execution plan.
>
> It may be extra overhead if you still keep the old index on the column
> ( assuming that you had one ) and the new function based index.
>
> Please do some resarching and think about what approach might work for
> you. Do some testing and validation in a test environment don't
> blindly make change to your production system.

Thank you very much! Received on Wed Jan 17 2007 - 16:28:55 CST