On 14/02/11 06:59, Gnanakumar wrote:
>
> How can we boost performance of queries containing pattern matching
> characters?
> QUERY: DELETE FROM MYTABLE WHERE EMAIL ILIKE '%domain.com%'
> As it is clear from the above query, email is matched "partially and
> case-insensitively", which my application requirement demands.
Well, for that exact pattern you're not going to find an index that's
much help. Do you really need something so wide-ranging though? The
above will match all of the following:
user1@domain.com
user2@sub.domain.com
user3@domain.com.au
user4@unrelated-domain.com
user5@unrelated-domain.com.au
user3@sub.domain.com.au
user4@sub.unrelated-domain.com
user5@sub.unrelated-domain.com.au
user6@sub.unrelated-domain.completely-wrong.com
Is that really what you are after? Or, did you just want to match:
user1@domain.com
user2@sub.domain.com
--
Richard Huxton
Archonet Ltd
