This thread generated a lot of responses and I'd like to thank everyone for helping me. Several came in after I left work yesterday and I want to answer those, but it seems the consensus is to use the tweak || ''.
Thomas, thanks for the brief explanation of index statistics. That's helps me a lot to understand what's going on. You mention some strategies involving stored procedures, but I'm not using them for this problem. Sean, you asked about whether I had tried your query that gets rid of the SQL 87 join syntax. Just for clarity, I didn't introduce that join syntax to the thread. That was done by Fabiano. My query in the original post used an explicit join. When I convert Fabiano's query to use the explicit join, it doesn't change the generated plan. But I can't use his approach anyway because he's doing the User_ID filtering in the inner query where I can't use it because this query will unioned with other queries to form a view. Svein, thanks for your input that using the || '' to tweak the optimizer is a common strategy. I guess I'll go with that as a solution. I'm guessing, as Thomas pointed out, that it is the differing selectivity and cardinality of the two indexes that is causing one to be used and the other not. Over time as my data changes, it may work out that the optimizer would eventually choose the correct strategy. I guess I'd also need to have a procedure to tell Firebird to recalculate the statistics for this to be true. I'll just have to leave myself a reminder to check this situation in a year or so. Thanks, all!
