I have a problem where the optimiser chooses a poor plan. The query is on a view and the problem is the choice of indexes in one of the tables in the view. Is there any way I can provide a hint in the query using the view to specify the index to use in the main query rather than within the view as there may be other queries that use the view when that hint would be unhelpful.
What I have is basically a list of cash transactions with an account_id and a ledger_date and there is an index on both of these. There is then a table of account groups which maps each group to a range of accounts. There are usually 3-10 accounts per group.
The query is looking for transactions for a single account group over a range of dates (the range can be quite large) and sometimes for no reason that I can fathom it seems to think it is a good idea to scan the range of dates and then filter by account rather than the other way around.
So I want to do something like this...(but the hint is ignored presumably because this index doesn't actually exist on the view)
from vwCashTransactions with (index (idx_account))
where groupaccountid = @P1 and ledgerdate between @P2 and @P3
Now I could do something hideous to get round it such as this but I though it would be clearer to other people what I was doing if I could do it with hints or something else.
where groupaccountid = @P1 and coalesce(ledgerdate,ledgerdate) between @P2 and @P3
Any other bright ideas?