Re: Using LIKE with indexed column

gtupper_at_avenger.nosc.mil (Greg Tupper) writes:

>A Question on Indicies and Like
 

>Environment
>Hp 755
>Hpux 9.01
>Oracle 7

>I have a table (45,000+ records) with a varchar2 (60) not null column, col1, with a
>unique index built on it.
 

>When I select a record and specify a particular value for col1 ( ie select * from table1
>where col1 = 'howdy doody time') , I get an answer back in 0.01 seconds. Rather faster
>that I expected.
 

>When I select a record using like (ie select * from table1 where col1 like 'howdy doody
>tim%' ) I must wait 60 to 220 seconds for the answer, even though there is only one
>record in the table that will match.
 

>I figured it would take longer, but I did not expect that kind of increase in processing
>time.
 

>It the index used to find the record?
>What could I do to speed it along?
>What exactly does Oracle do? (I love the details, helps me remember things)
 

>Thanks for any input.
 

>Greg Tupper
>Saic Comsystems
 

>gtupper_at_nosc.mil

Using "Like" causes the optimizer to do a full table scan. You are not using the index in that particular query.

   -Pat Connors Received on Thu Apr 20 1995 - 00:00:00 CEST