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
--
Mark Rotteveel