Hi, i do not know what should happen in your case when FIEL3 is null? But you can try
WHERE FIEL1=:F1 AND FIEL2=:F2 AND (FIEL3=COALESCE(:F3, FIEL3) ) INTO .. DO .. regards, Karol Bieniaszewski From: mailto:[email protected] Sent: Wednesday, August 19, 2015 1:29 PM To: [email protected] Subject: Re: [firebird-support] Re: MAKE 'EXECUTE STATEMENT' USE INDEX ok,thanks This time,adding condition this way WHERE FIEL1=:F1 AND FIEL2=:F2 AND (FIEL3=:F3 OR :F3 IS NULL) INTO .. DO .. is causing query to be much slower ... this WHERE FIEL1=:F1 AND FIEL2=:F2 AND (FIEL3=:F3 & nbsp; ) INTO .. DO .. takes 0.563s 82655 fetches, 1088 marks, 1584 reads, 50 writes. 197 inserts, 98 updates, 148 deletes, 19166 index, 66 seq. Delta memory: 11536 bytes. Total execution time: 0.563s but this takes&nbs p;29.141s WHERE FIEL1=:F1 AND FIEL2=:F2 AND (FIEL3=:F3 OR :F3 IS NULL) INTO .. DO .. 1700290 fetches, 1065 marks, 3694 reads, 77 w rites. 198 inserts, 99 updates, 149 deletes, 29422 index, 68 seq. Delta memory: -71656 bytes. Total execution time: 29.141s Script execution finished. in both cases :F3 is NOT NULL thanks From: "Dmitry Yemanov [email protected] [firebird-support]" <[email protected]> To: [email protected] Sent: Wednesday, August 19, 2015 11:51 AM Subject: [firebird-support] Re: MAKE 'EXECUTE STATEMENT' USE INDEX 19.08.2015 10:26, 'Mr. John' wrote: > now index is used if I call > SELECT * FROM MYPROC(...) > but I use it in other procedures,no join on it just simply SELECT .. > FROM MYPROC(...) in this case I can see in the plan : > ...(MYPROC NATURAL)... That' OK. In complex plans, (MY_PROC NATURAL) just means a select from a procedure, it says nothing about actual plans inside MY_PROC. Dmitry
