Re: Oracle trim in a where clause

Lionel wrote:
> Hi !
>
> I have a performace problem on a SQL statement.
> I have to join a table column of type CHAR(100) over a dblink (the table
> comes from an external provider and can't be modified)
> My identifier value comes from a sequence.
> The dblink's table column identifier is formatted as (SomeChars || myId) +
> the spaces of the CHAR.
>
> It seems that using:
> select myColumn from myTable a, DbLinkTable b where 'SomeChars'||a.myId =
> trim(b.id)
> performs quite bad.
>
> I tried to append 88 spaces after my ID to avoid the trim: it is fast, but
> quite ugly (I'll have to remember to remove one whitespace when my
> sequence's length increases).
>
> Anyone sees a solution ?
> Thanks
>
> PS: The only solution I see would be to add a CHAR(100) column to my table,
> which would contain (SomeChars || myId).
> I would like to avoid that...

For a start consider using the LPAD function in order to void padding manually.
Also consider using a Function Based Index on 'SomeChars'||a.myid. Then consider hiring lawyers ...

-- 
Sybrand Bakker
Senior Oracle DBA