Re: Oracle trim in a where clause

Lionel wrote:
> sybrandb <sybrandb_at_gmail.com> wrote:
> > For a start consider using the LPAD function in order to void padding
> > manually.
>
> Great ! I didn't know there were LPAD and RPAD functions.
> RPAD('SomeChars' || myId, 100, ' ') will suit perfectly.
>
> > Also consider using a Function Based Index on 'SomeChars'||a.myid.
> > Then consider hiring lawyers ...
>
> To sue the person who decided to use a CHAR(100) ? :)
>
> And what if I create a view on the dblink that would do a
> to_number(substring(trim(otherTable.charId)) ?
> That would allow a join on a number column.
> Would it be better or worst ?

It might be ok. The point being made was that when you have stuff on the left side of a SQL statement ( where some_combination_of_functions(column_specification) = whatever ) there are a couple of things to consider.

First, if you can eliminate that some_combination_of_functions on the left side it would probably be very beneficial. Oracle tends to not do well with functions on columns even if there are indexes on those columns.

Second, if you absolutely have to have that " some_combination_of_functions " ( one or more functions ) then you still may be able to get decent performance if you build a function based index that uses that same " some_combination_of_functions ".

In your case yes perhaps you might be able to build a function based index on the ( substring(trim(column name ) ) or even the to_number( same stuff on inside ) ... you would want to test it out carefully and compare the performance. In general putting to_number on character fields you want ot be very careful about what data might eventually make it into to ( attempted ) to_number conversion.

The function based indexes are relatively straight forward. Combining a view and then using them might also be possible but proceed slowly and step by step before getting too complicated. Received on Mon Oct 02 2006 - 07:12:08 CDT