>I have a desc index on table GERMINATION - I_GERMINATION_DATE_DESC. > >When I run either one of these queries I get > "index I_GERMINATION_DATE_DESC cannot be used in the specified plan" > > Select G.GERM from Germination G > PLAN (G ORDER I_GERMINATION_DATE_DESC) > > Select G.GERM from Germination G > PLAN (G INDEX(I_GERMINATION_DATE_DESC)) > >The following is successful and in DBWorkbench shows Plan = "PLAN (G ORDER >I_GERMINATION_DATE_DESC)" > > Select G.GERM from Germination G order by G.GERM_DATE Desc > >I am trying to use the PLAN because this Select is used as a subquery in a >view where I cannot specify an order by. > >I have never used Plan before, so what am I missing?
Hi Rick! Explicitly specifying a plan can be OK if you have a specific query with performance trouble and after analyzing the problem find that there's no better way to solve it. Though that does mean that you tell the optimizer that you know how it should do its task, and that it shouldn't try to improve things. Myself, I very rarely write explicit plans (though considerably more frequently restrict the optimiser a bit by adding +0 to fields for which I don't want it to use an index). My understanding of subqueries is that they should return only one row, and I do not understand how you are using your statement as a subquery. Could you elaborate a bit, possibly showing us the entire CREATE VIEW statement? Set
