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

Dan Clamage 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

Be careful with this suggestion!

   While it is true that a function can be evaluated in the index, a full scan of the index structure is required, since there is no entry point for the normal index tree-search.

   If *any* of the columns referenced in the query are not part of the index, each qualifying index entry then results in a random access to a data block .

   Unless the index segment is small in comparison to the data segment AND the proportion of rows returned is small ( <15% of the total rows), using the index may take longer than a serial scan of the data segment.

   It did so by a factor of two in the case I tested (with a 5% hit rate)!

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"