Re: Binding on a char column

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: 17 Jan 2007 06:30:56 -0800
Message-ID: <1169044256.730828.306760@a75g2000cwd.googlegroups.com>

silvia.fama wrote:
> Thank you very much!
> I believe that the best is to use varchar instead of char.
>
> I hoped oracle may give me the possibility to set an option or
> parameter that allows me not to use RTRIM with bind variables. I
> suppose it doesn't exist, and your very useful suggestion are the only
> way to resolve this problem!
> May you confirm it?
>
> Thank you and kind regards!

You lost me a little on your question above Silvia.

Oracle has to look at the SQL that you send in and the where clause. When the cost based optimizer see's a function ( like RTRIM ) on the left hand side ( RTRIM(some_column = 'value' or :bind_variable ) it checks indexes that it has against the column.

If there is only a "regular" index on the some_column and not an index ( RTRIM(some_column ) )that includes the function ( and matches the WHERE clause construct ) the optimizer usually decides that it can't or won't try to use than index.

Have you looked at the Tom Kyte site http://asktom.oracle.com ... try doing some searches on "char" and "varchar" ... lots of good information there. Received on Wed Jan 17 2007 - 08:30:56 CST