I have a really strange index-problem with a lookupquery. But before
some background info. I have a table CUSTOMERS with a col CSSAPNR and a
index I_CSSAPNR_A on this col, which is not unique and ascending. On a
form I show all customers ordered by CSSAPNR and opening the query takes
0,03 sec. I deactivate the index, opening takes 1,4 sec and after
activating it again it takes 0,03 sec. That's all normal and as
expected.
Now I have form with all CONTACTS. Every Contact belongs to a customer
linked over a foreign key. The customers-table is now a lookup-query for
contacts and the customers are ordered by CSSAPNR again. After I open
the contacts-query, the customers-query will be opened. Opening the
Customers-query takes 0,03 sec. I deactivate the I_CSSAPNR_A-index and
it takes 1,4 sec. But when I now activate the index again, it still
takes 1,4 sec !!!! It seems, that this lookup-query doesn't use the
index, but the plan shows, that the index is taken.
This behaviour is reproducable. When I insert records in a empty
database, the lookup-query works as fast as the normal query. Now I
deactivate and activate the index, the normal query works as fast as
before, but the lookup-query works as if the index is still deactivated.

I really go crazy here. Has anybody a idea, what could be the reason for
this behaviour ??


Guido