Hi.
In one of our applications that the drop down menu was taking a lot of time to expand (at least 3-4 minutes or more) after creating an index on one of the columns in the select statement used for this application it started working fine but after a few weeks the same problem occurred. When I looked at SQL tuning advisor it showed that the index we created is not being used anymore instead it was using the PK column as the index for the exact same sql. We first ran stats on the impacted table and then we ran stats on the whole schema and it started using our index once again.This was on top of the table stats we gather for that table every day early in the morning which makes us think the stats on the table were up-to date. I am not sure how and why the CBO switched to a PK index instead of the index we created. Appreciate your advise and suggestions in understanding and resolving this!
Database Version:11.2.0.2
OS:HP-UX