Den 18.08.2015 16:59, skrev 'Mr. John' mr_joh...@yahoo.com
[firebird-support]:
HI,in SP I have this query
FOR EXECUTE STATEMENT 'SELECT SUM(CANT) FROM TABLE1 WHERE
FIEL1='||:F1||' AND FIEL2='||:F2|| IIF(:pCondition=1,' AND
FIEL3='||:F3,'') INTO .. DO ..
TABLE1 PK =FIELD1,FIELD2,FIELD3
running the query gives a NATURAL PLAN
How to make it use the index?
Index is used if I change to:
FOR SELECT SUM(CANT) FROM TABLE1 WHERE FIEL1=:F1 AND FIEL2=:F2
AND FIEL3=:F3 INTO .. DO ..
but this way I can't use the IIF condition that I need
thanks!
Why would you need an IIF condition? What about
FOR SELECT SUM(CANT) FROM TABLE1 WHERE FIEL1=:F1 AND FIEL2=:F2 AND
(FIEL3=:F3 or cast(:pCondition as SmallInt) is distinct from 1) INTO ..
DO ..
That could at least use an index for FIEL1 and FIEL2 (if they are the
first two fields in your PK, that index could be used). Now, in some
cases the index may have poor selectivity so that it still chooses
natural (you didn't say whether "FOR SELECT SUM(CANT) FROM TABLE1 WHERE
FIEL1=:F1 AND FIEL2=:F2 INTO .. DO .." uses an index or not). If so, the
only way to use the index would be to use IF in your procedure and two
separate queries, the index being used if you run the query comparing
with FIEL3, but no index used when you run the query with only FIEL1 and
FIEL2.
I've seen Dmitry Yemanov answer a different question (that may or may
not be similar to yours) with repeating the first part and that could
possibly translate to:
FOR SELECT SUM(CANT) FROM TABLE1 WHERE (FIEL1=:F1 AND FIEL2=:F2 AND
FIEL3=:F3) or
(FIEL1=:F1 AND FIEL2=:F2 AND cast(:pCondition as SmallInt) is distinct
from 1)
I never quite understood why I saw a similar statement to this, but I
assume it doesn't hurt trying if my first suggestion fails.
Set
