--- In firebird-support@yahoogroups.com, "kick_tisho" wrote:

> --- In firebird-support@yahoogroups.com, Svein Erling Tysvær
> <svein.erling.tysvaer@k...> wrote:
> > Hi Stanislav
> >
> > > 2. To write my own plan for such big queries (more of them with
> > > 10 filters and more) seems like an absurd
> >
> > This isn't possible, the query simply cannot use an index when you
> > use OR <something without an index>. If you want to use the index
> > where possible, you have to rewrite your procedure to something
> > like
> >
> > IF (:P_IMPORTID IS NULL)
> > BEGIN
> > FOR
> > SELECT
> > C.ID
> > FROM
> > CONTACTS C
> > DO
> > SUSPEND;
> > END
> > ELSE
> > BEGIN
> > FOR
> > SELECT
> > C.ID
> > FROM
> > CONTACTS C
> > WHERE (C.IMPORTID = :P_IMPORTID)
> > DO
> > SUSPEND;
> > END
> > END;
>
> The problem with this is that i have 20 filter params per procedure.
> and the select itself returns 40 params. It will be big code
> duplication.

Well, yes, maybe this isn't the solution in your case. In theory, that
could at worst mean more than a million if-elses But I doubt that all
20 parametres are used equally much by themselves and that all of them
would benefit from using an index. Maybe you can cut down the number
of likely alternatives to just a few making this a feasible solution,
and maybe not. If feasible, all unlikely alternatives could go into a
separate statement that never used any index (like your procedures
currently do).

Set