SQL & PL/SQL

Hi,

I'm querying one of the Oracle Applications Standard Table. The performance is very slow when like operator is used in the query condition. The query uses a indexed column in the where clause.

The query is..

select * from hz_parties
where upper(party_name) like '%TOY%'

In the above case, It is not using the index and doing full table scan. I have checked the explain plan and the cost is 4496.

select * from hz_parties
where upper(party_name) like 'TOY%'

If I remove the '%' at the begining of the string, the performance is good and it is using the index. In this case, the cost is 5.

Any ideas to improve the performance of the above query. I have to retrieve the records whose name contains the string. I have tried hints to force the use of index. But it is of no use.

Thanks,
Rama