alternatives for LIKE predicate 1

[logic4fun]

hi all,
i am running a query which hits a huge table with a billion records and uses a like predicate...here is the query..
select *
from table T
where T.value like '% and %'

i cant ignore the starting % or ending % coz..the value can be anywhere in the string.. like andy...or durand or mandy..so as this particular query is going row by row and scanning the whole database..it is taking hell lot of time..to execute..
IS THERE ANY WAY TO USE SOME ALTERNATIVE FOR "LIKE" PREDICATE..WHICH SPEEDS UP MY QUERY..
thanks in advance..
logic4fun

 

[MarcLodge]

I can't immediately think of any other way that will give you the results you require. You might try posting to the ANSI SQL forum as some of the people over there do things with SQL that make my brain hurt.
Marc

 

[gregsimpson]

logic4fun,

I notice you are doing a select *. This maybe just for your example here but good practice dictates you select only the columns you are interested in.

That said if you can select only the fields your interested in put all the fields in an index you may retrieve a better result. Even if the whole index has to be scanned, this is quicker than scanning the whole table. That is also true if you index the whole table as DB" uses something called index overcrowding. This effectively means it will fit as many indexes into a page as it physically can but will only put 255(or 256 can't remember) rows into a page. As there is no physical limit with the index, it can scan the pages much faster. Index only access is usualy the fastest for retrieval.

Cheers
Greg

 

[blom0344]

Just a crazy proposition (cause it is likely to be slower...) but:

Select column1,column2,column3 from table where
REPLACE(column1,'and','') <> column1

T. Blom
Information analyst
tbl@shimano-eu.com

 

[udbdb2]

I think you meant Index Overloading rather than overcrowding . I found the post from a search engine and it's sorted my problem anyway.Thanks Greg

Thanks Mark