Re: Binding on a char column

silvia.fama wrote:
> Hi!
> I'm using bind variables to execure select sql queries on my oracle
> database (version 9 and version 10).
> My database contains some char columns (char (50), char(1), etc)
>
> Using bind variables I have a problem with trailing blanks at the end
> of the string in a char column. I mean that if I insert in a column of
> char(10) type 'test' string, I will have in oracle db 'test '
> string (with trailing blanks).
> When I bind that column searching for 'test' string I'd need to RTRIM
> the column:
> select * from TABLE where RTRIM(col1) =:bind1
> where bind1 will be associated to test.
> If my query is:
> select * from TABLE where col1 =:bind1
> I will not have the result expected if bind1 is 'test', I should bind
> 'test ' with blank.
>
> So, I know that RTRIM is not good for performance, does oracle gives me
> the possibility to work into another way?

When you use char instead of varchar2 column definitions in oracle and index them you basically have 2 good choices when working with bind variables in queries:

1. include in the bind variable value that you are using additional spaces to the end ... this will match how oracle fills up the char column with extra spaces
2. create a function based index on the column that matches the SQL that you will be using in your where clause

Option 2 works pretty well when all the queries that you are using on that column work the same way ... they all include the same function against the char column. Or you can consider have 2 indexes one on just the char and the second a function based index but that's extra overhead.

A good alternative to consider is using varchar2 instead of char. As far as I know, Tom Kyte is not a big fan at all of using char and ( my impression is ) pretty much advocates doing everything in oracle with varchar2. Received on Wed Jan 17 2007 - 07:31:11 CST