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

I gotta stupid question. Does the developer really give two hoots and a holler about doing a full scan of either the table or the index?

Looking back at the original request I am not sure but I suspect the problem is more one of keeping the data entry/data entry processing clean and simple than a concern about processing efficiency.

Since the original poster did not say why the developer didn't want to use LIKE we are all shooting in the dark.

If, for example, the table they want to search (which appears to be a customer name table) has less than 10,000 rows -- which should suffice for most organizations selling to business accounts -- full scans aren't going to be very expensive in relation to the time spent entering the values to be retrieved. Of course if their customer table has a million rows then :-(

Jerry

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

snip

> > 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
>
>
Received on Sat Sep 27 1997 - 00:00:00 CDT