At 09:03 AM 15/07/2003 +0000, you wrote:

>Thanks Markus.
>
>Does anyone know whats the performance of this kind of sql?
>
> select *
> from customer
> where (type = :paramtype) or (Cast('*' as VarChar(3)) = :paramtype)
>
>Does it uses the indexes properly? how about on large table?

No, it doesn't use indexes at all, because there are no database columns in
the OR'ed part of the criterion.

However, that is not necessarily a bad thing for this style of query. If
your "type" column consists of only a few possible values spread across a
large table, then any index on it would be of extremely low selectivity and
will make the query very, very slow. That's what Svein is talking about,
re using this trick deliberately to prevent the index being used.

btw, SQL tricks and tips are OFF TOPIC here - this list is about IBO. Use
firebird-support for SQL questions, please, and don't cross-post.

Helen