Hello all;
I'm using 7.6.00.09 on Windows XP, and I've a situation where suddenly the
optimizer goes nuts and ceases using an [obvious] index resulting in severe
performance slump.
The index is defined as following:
CREATE INDEX "AVAILIDX" ON "FDB"."TICKETS"("L_ID" ASC, "AVAILABLE" DESC)
And at the moment there are 100.000 rows in the table, and the column
"AVAILABLE" is predominantly set to 'Y'. The other possible value is 'N'.
When querying the optimizer this is the conclusion:
explain select * from tickets where available='Y' and l_id =
'55039423590512150001' and Rowno <= 1 // this is the query the
application uses
Gives a table scan and cost of 1603 while
explain select * from tickets where available='N' and l_id =
'55039423590512150001' and Rowno <= 1
Gives an index scan and cost of 16
I've tried to execute the query in multiple versions, and saving the
optimizer statistics in-between, rebuild the index, etc., but to no luck
(this worked for me on the same db few months ago). Is there any means to
force the optimizer to use the index?
Best regards,
Helgi.
