Re: Why is Oracle doing a full table scan on a like?

Dixon2_at_ix.netcom.com wrote:
>
> If I run a select that does a like command why does it do a full table
> scan? I would understand it if the first character was a wildcard but
> it is doing the full scan no matter how I enter it.
>
> Ex.
> Select first_name, last_name
> from emp
> where last_name like 'Smi%';
>
> This will do a full table scan but it looks like it should take the
> last_name index and be able to start at 'Smi' and go to 'Smj'. I
> thought I saw this in the Oracle SQL ref book.
>
> Any help on this would be appreciated
> Mike Dixon
> Dixon2_at_ix.netcom.com

If the emp table is relativly small, the cost-based optimizer may decide its not worth the effort, and just do a full table scan. Otherwise, this should use an index on last_name. LIKE will use indexes, as long as there are no functions operating on the field name (not: UPPER(last_name) LIKE 'SMI%') and there are characters before the wildcard (not: last_name LIKE '%ith').

-- 
-------------------------------------------------
Ken Johnson -  Technical Consultant
Tapestry Computing, Inc. http://www.tapestry.com