Re: HELP: select ... like speed issues

A copy of this was sent to "OnlineJobStore.com" <info_at_onlinejobstore.com> (if that email address didn't require changing) On Wed, 02 Jun 1999 19:03:02 -0400, you wrote:

>Hi everyone,
>
>Here's the deal:
>- Oracle 8 running on NT box;
>- there is a table that contains around 100,000 rows;
>- each row contains a field (VARCHAR2) with 3000-4000 characters of
>text;
>- it is needed to run LIKE query against the field
>
>So far, the straightforward approach does not give satisfactory results
>and does not scale well either. Any ideas about it? Please do not
>mention "upgrade hardware" solution :)
>
>Thank you for help,
>Victor Urvantsev

If it is an arbitrary 'like' such as "like %a%", then indexes will do no good. A full scan is probable. What I've done in the past, with good success, is to create a table that is as packed as possible (create table as select with a pctfree of 0) and use the like on that. for example, lets say you have a table T with lots of columns and the one you are going to 'like' on is column X. I would:

create table T_skinny pctfree 0 as select rowid rid, x from T; alter table t cache;

Then, write the query as:

select * from t
where rowid in ( select rid from t_skinny where x like '%a%' );

Now, the full scan works in a 'skinny' table and can full scan it much faster. this only works is T is relatively static in nature....

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jun 02 1999 - 21:16:04 CDT