Re: Binding on a char column

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. Received on Wed Jan 17 2007 - 09:30:55 CST