 <p>without understanding way more than I care to know about your data model and you business it's hard to give concrete positive advice. But here are some notes about your indexing strategy and why I would guess the optimizer is not using the indxes you have. </p> <p>In the sub-query the access path to REDLINE_MSATTRIBUTE drives from three columns: </p> <ul> <li>CLASS</li> <li>OBJECT_ID</li> <li>CHANGE_RELEASE_DATE.</li> </ul> <p>CLASS is not indexed. but that is presumably not very selective. OBJECT_ID is the leading column of a compound index but the other columns are irrelevant the sub-query.</p> <p>But the biggest problem is CHANGE_RELEASE_DATE. This is not indexed at all. Which is bad news, as your one primary key look up produces a date which is then compared with CHANGE_RELEASE_DATE. If a column is not indexed teh database has to read the table to get its values. </p> <p>The main query drives off </p> <ul> <li>ATTID</li> <li>CHANGE_ID</li> <li>OBJECT_ID (again)</li> <li>CHANGE_RELEASE_DATE (again)</li> <li>CLASS (again)</li> <li>OLD_VALUE</li> </ul> <p>ATTID is indexed but how sleective is that index? The optimizer probably doesn't think it's very selective. ATTID is also in a compound index with CHANGE_ID and OLD_VALUE but none of them are the leading columns, so that's not very useful. And we've discussed CLASS, CHANGE_RELEASE_DATE and OBJECT_ID already.</p> <p>The optimizer will only choose to use an index if it is cheaper (fewer reads) than a table scan. This usually means WHERE clause criteria need to map to the <em>leading</em> (i.e. leftmost) columns of an index. This could be the case with OBJECT_ID and ATTID in the sub-query except that</p> <ol> <li>The execution plan would have to do an INDEX SKIP SCAN because REDLINE_MSATTRIBUTE_INDEX1 has CHANGE_ID between the two columns</li> <li>The database has to go to the table anyway to get the CLASS and the CHANGE_RELEASE_DATE. </li> </ol> <p>So, you might get some improvement by building an index on <code>(CHANGE_RELEASE_DATE, CLASS, OBJECT_ID, ATTID)</code>. But as I said upfront, without knowing more about your situation these are just ill-informed guesses. </p>