SQL Performance (MOSC)

MOSC Banner

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center