Incorrect Index used randomly for the same Query
We recently encountered a production issue where the database randomly chooses different indexes for running a specific query. On certain days it uses the PK_A index whereas on certain days it chooses TUNE_B index. On days when the system uses the PK_A index, the query takes approximately one hour to finish. However, when the system choses the TUNE_A index, the query completes in under two seconds.
No. of Record in Table: 345,692,102
Index Name | Unique | Logging | Degree | Columns | Order |
---|---|---|---|---|---|
TUNE_V_REF_NO | N | NO | 1 | V_REF_NO | ASC |
TUNE_BILL_TRANS | N | NO | 1 | V_POLICY_NO |