Subject Re: [firebird-support] CONTAINIG vs. LIKE
Author Aldo Caruso
Mark,

thanks for your answer. (the page number I mentioned was on the first
edition ).

Aldo Caruso

El 04/08/14 a las 11:50, Mark Rotteveel mark@...
[firebird-support] escibi├│:
> On 4-8-2014 16:42, Aldo Caruso aldo.caruso@...
> [firebird-support] wrote:
>> Hello,
>>
>> I have three questions relating CONTAINING predicate.
>>
>> In "The Firebird Book" I read on page 318 that indexes are used
>> also on search conditions against CONTAING predicates. I did some test
>> and, inspecting its PLAN, I found that it uses NATURAL order instead of
>> using an index.
>>
>> 1) ┬┐ Under which circumstances an index is used when the search has a
>> CONTAINING condition ?
>>
>> Given the following two SQL clauses
>>
>> SELECT * FROM TABLE1 WHERE UPPER(FIELD1) LIKE '%TEST%'
>> SELECT * FROM TABLE1 WHERE FIELD1 CONTAINING 'TEST'
>>
>> 2) Are they logically equivalent ?
>> 3) Which of them is faster ?
> They are logically equivalent, and I'd assume they perform similar
> (although a CONTAINING might have the benefit that it doesn't need to
> support more complex patterns). I am not 100% sure, but I think Helen's
> book is wrong here (btw: page 270 in The Firebird book second edition).
>
> Mark