Hi Adam,

> Don't duplicate the index. Do you mean you only had a unique
> constraint on NAME but you added an ascending index and a descending
> index? If so, the unique constraint will automatically add an
> ascending index, so there is no need to add yet another index.

i was just pre-empting the possibility of someone suggesting both an
ascending and descending index.

> The larger question is whether there is a need to solve this problem
> at all.
>
> ---
>
> In another post, you wrote:
>
> WHERE TITLE.METAPHONE = METAPHONE('after the ball')
> OR TITLE.NAME CONTAINING 'after the ball'
>
> And in another post I asked:
>
> 1) Why do you need the OR condition?
>
> If anything is guaranteed to match a soundex, it is an identical
> string. Just seems like extra work with no benefit to me.

this follows on from my original post 'trying to avoid large datasets'
on july 7 where i didn't want to select all products to list, but to
be able to list products that match whats being searched for and
products that may be the same but have a spelling mistake, hence the
use of a metaphone. i added OR CONTAINING because it's case
insensitive and because i also want to match things that may have a
different metaphone but contain the title that someone is looking for.

> ---
>
> If your title was, 'Running after the ball' then it may not appear
> identical in your metaphone function, but then if you want to use an
> index, you cant use containing. You would need Starting With or = or
> a Like that can be optimised into a Starting With to make use of an
> index. With containing, the metaphone index would not be useful
> because even the records that do not need to be considered by the
> metaphone index still need to be considered by the containing clause.

so all i was trying to figure out is how to find a title containing
the title being searched for (case insensitively) in a way that
doesn't slow the query down.

i added another field TITLE.UPPER_NAME that gets updated in a trigger
with the NAME in uppercase. using anything aside than STARTS WITH or =
slows the query down.

in the end having the OR CONTAINING condition isn't crucial so i think
i might forget about it for the moment and if i need to i can always
come back and annoy you later :-)

thanks,
mark