Why oracle table indexed but still do full table scan?

Question

I have a table 'MSATTRIBUTE' with 3000K rows. I used the following query to retrieve data, this query has different execution plan with same DB data but in different env. in one env, it appears full scan so the query is very slow, but in another env it all used index scan it's quite good, everybody who knows why it have full table scan in one env because I built index for them, how do I let become index scan just like what I tested in env 1. how I can improve this query?

Answer 1

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.

In the sub-query the access path to REDLINE_MSATTRIBUTE drives from three columns:

• CLASS
• OBJECT_ID
• CHANGE_RELEASE_DATE.

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.

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.

The main query drives off

• ATTID
• CHANGE_ID
• OBJECT_ID (again)
• CHANGE_RELEASE_DATE (again)
• CLASS (again)
• OLD_VALUE

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.

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 leading (i.e. leftmost) columns of an index. This could be the case with OBJECT_ID and ATTID in the sub-query except that

1. The execution plan would have to do an INDEX SKIP SCAN because REDLINE_MSATTRIBUTE_INDEX1 has CHANGE_ID between the two columns
2. The database has to go to the table anyway to get the CLASS and the CHANGE_RELEASE_DATE.

So, you might get some improvement by building an index on (CHANGE_RELEASE_DATE, CLASS, OBJECT_ID, ATTID). But as I said upfront, without knowing more about your situation these are just ill-informed guesses.