From: barry_roomberg@iacnet.com (Barry Roomberg)
Subject: Re: Using LIKE with indexed column
Date: 1995/04/18
Message-ID: <D78Hsr.4ux@zcias2.ziff.com>#1/1
sender: news@zcias2.ziff.com (USENET News System)
references: <1995Apr18.082719.17379@nosc.mil>
organization: Data Sources
newsgroups: comp.databases.oracle


In article <1995Apr18.082719.17379@nosc.mil>, gtupper@avenger.nosc.mil (Greg Tupper) says:
>
>
>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.
>

Should be just as fast as the exact =.  Sounds like an optimizer problem.  Make 
sure you are using RULE based and not COST based (look into alter session and/or)
init.ora params) and drop any statistics associated with that table and try again.