Re: Forcing use of an index

Bob P <bob_pombrio_at_yahoo.com> wrote:
: 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

: 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.

A rather weird way of doing it which might just work: Create function-based indexes on substr(load_id,2), substr(load_id,3), ... and rewrite your query as

Select (some stuff) From a
where
( load_id like '7339%' or substr(load_id,2) like '7339%' or ... )  And other columns = values

   Peter

-- 
Peter Marksteiner
Vienna University Computer Center