Hi,

I have the following problem with the Plan Analyzer.

let's say i have a simple procedure GETCONTACTS(:P_IMPORTID) which returns recordset with Contacts from the CONTACTS table.

the SQL query in the procedure looks like this

FOR
SELECT
C.ID
FROM
CONTACTS C
WHERE (:P_IMPORTID IS NULL OR C.IMPORTID = :P_IMPORTID)
DO
SUSPEND;

my logic here is if i pass NULL for :P_IMPORTID to GETCONTACTS it will return all Contacts.
if i decide to get all Contacts which comes from import 2, i will query SELECT * FROM GETCONTACTS(2)

the C.IMPORTID is FK (indexed col)

let's say i have 300.000 records in the contacts table and i want to get the Contacts for import 2

the plan analyzer, doesn't use the C.IMPORTID FK index and makes natural read of persons,
because of this -> :P_IMPORTID IS NULL, it seems that he aways evaluates this.
it seems that when i have (1=2 OR C.IMPORTID = :P_IMPORTID) it never use the index for this filter

The problem in our application is that we used many GET procedures on which we pass filter params like this, and it seems that Firebird don't use the indexes.

What are my possible solutions to this.
1. Dynamic queries don't seems to me very suitable as they are hard to concat.
2. To write my own plan for such big queries (more of them with 10 filters and more) seems like an absurd
...

If somebody can help me to find a solution i'll be very greatfull.

Best wishes, Stanislav Stratiev.


[Non-text portions of this message have been removed]