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

Oracle optimizer is not always correct choosing the optimal access path.

However, sometimes it is smarter than we are.

If you have a table that can be read in a few blocks then a full table scan (FTS) is more I/O efficient than reading the index then locating the data from the index in a data block, repeat for each record.

If an index is the better use of I/O, please review Oracle 'hints.' This will help you to create an 'index' hint.

Another issue is "bind peeking." This cannot be covered in this comment, so your research is required and how 'histograms' are collected on data.

Answer 2

when oracle has an index and decides to use/not use it it's might be because 1) you may have different setting for OPTIMIZER_MODE - make sure it's not on RBO. 2) the data is different - in this case oracle might evaluate the query stats differently. 3) the data is the same but the stats are not up to date. in this case - gather stats

dbms_stats.gather_table_stats('your3000Ktable',cascade=>true);

4) there are allot more reasons why oracle will not use the index on one environment, i'll suggest comparing parameters (such as OPTIMIZER_ INDEX_COST_ADJ etc...)

Answer 3

One immediate issue is this piece SELECT RELEASE_DATE FROM CHANGE WHERE ID = 136972355 (This piece of code will run for every row coming back and it doesn't need to... a better way of doing this is using a single cartesian table so it only runs once and returns a static value to compare....

Example 1:

Select * From Table1, (Select Sysdate As Compare From Dual) Table2 Where Table1.Date > Table2.Compare.

Is always faster than Select * from Table1 Where Date > Sysdate -- Sysdate will get called for each row as it is dynamic function based value. The earlier example will resolve once to a literal and drastically faster. and i believe this is definitely once piece hurting your query and forcing a table scan.

I also believe this is a more efficient way to execute the query.

Select  
            REDLINE_MSATTRIBUTE.ATTID
           ,REDLINE_MSATTRIBUTE.VALUE
  From 
            REDLINE_MSATTRIBUTE
           ,(
                SELECT  ATTID
                        ,CHANGE_ID
                        ,MIN(CHANGE_RELEASE_DATE) RELEASE_DATE
                 FROM   REDLINE_MSATTRIBUTE
                       ,(SELECT RELEASE_DATE FROM CHANGE WHERE ID = 136972355) T_COMPARE                
                WHERE   CLASS             = 9000
                  And   OBJECT_ID           = 32718015
                  And   CHANGE_RELEASE_DATE > T_COMPARE.RELEASE_DATE
                  And   ATTID IN (1564, 1565)
                GROUP 
                   BY   ATTID,
                        CHANGE_ID
            ) T_DYNAMIC
Where         
        REDLINE_MSATTRIBUTE.ATTID = T_DYNAMIC.ATTID   
    And REDLINE_MSATTRIBUTE.CHANGE_ID = T_DYNAMIC.CHANGE_ID
    And REDLINE_MSATTRIBUTE.RELEASE_DATE = T_DYNAMIC.RELEASE_DATE
    And CLASS     = 9000
    And OBJECT_ID = 32718015
    And OLD_VALUE ='Y'
Order 
   By   REDLINE_MSATTRIBUTE.ATTID,
        REDLINE_MSATTRIBUTE.VALUE;  

Answer 4

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.

Answer 5

If the rows are in a different order in the two tables then the indexes in the two systems can have different clustering factors, and hence difference estimated costs for index access. Check the table and index statistics, including the clustering factor, to see if there are significant differences.

Also, do either of the systems' explain plans mention dynamic sampling?