Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to search on a column without using LIKE?
The solutions so far are no simpler than using the 'like' operator;
you might try
Select whatever from table_name where org_name in
('stans','widgits',...);
John Greco
"Dan Clamage" <clamage_at_mime.dw.lucent.com> wrote:
>> The use of a function, like instr(), disallows the use of an index on
>> org_name - resulting in a table scan.
>Unless you offer the optimizer a HINT to use a particular index.
>SELECT /*+ INDEX (TABLE_NAME INDEX_NAME) */
> *
>FROM TABLE_NAME
>WHERE
> instr( org_name, 'stans' ) > 0
> instr( org_name, 'widgets' ) > 0;
>
>Note that if you specify an alias for the table, use the alias in the hint.
>Then verify the optimizer uses the index by running an EXPLAIN PLAN.
>
>- Dan Clamage dclamage_at_idcomm.com
>
Received on Fri Sep 26 1997 - 00:00:00 CDT