Re: How to search on a column without using LIKE?

> 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