Forcing use of an index

I have a nasty little problem going on here and I'm open to suggestions.

First the set up =

Table - A
  load_id vchar2(12) not null
.
.

 (other columns)

Index IA1 (not primary key)
  load_id, (other columns)

Now the query:
Select (some stuff) From a
 where load_id like '%7339%'
   And other columns = values
.
.

You get the picture.

Because of the leading '%' in the query the index is not used. The problem comes in that the table has several million rows and is hit with this query several thousand times a day. I don't have a choice about the leading and trailing '%'s, they are a user requirement and I can't force the entry of the data to eliminate them either. In other words my input, 7339 in this case, can be in any portion of the column, in the start middle or end.

Any idea's how I can force the index to be scaned instead of the table? Or any better ways to do this query? If it helps the load_id column can be alphanumeric but my input will always be numeric only.

Thanks! Received on Thu Mar 17 2005 - 07:19:31 CST