Geek asks and answers

Why is the oracle table indexed but still doing a full table scan?

I have a table "MSATTRIBUTE" with 3000K rows. I used the following query to retrieve data, this query has a different execution plan with the same database data, but in different conditions. in one env, it looks complete, so the query is very slow, but in another env they all used the index, scanning it well enough, everyone who knows why it has a full table scan in one env, because I built for them index, how do I get the index scan to be the same as what I tested in env 1. How can I improve this query?

+5
source share
4 answers

without understanding the way more than I want to know about your data model, and it’s very difficult for you to give concrete positive recommendations. But here are some notes about your indexing strategy and why I assume that the optimizer does not use the indexes you have.

In the subquery, the access path to REDLINE_MSATTRIBUTE is controlled from three columns:

  • CLASS
  • OBJECT_ID
  • CHANGE_RELEASE_DATE.

CLASS is not indexed. but this, apparently, is not very selective. OBJECT_ID is the leading column of the composite index, but the other columns are not related to the subquery.

But the biggest problem is CHANGE_RELEASE_DATE. It is not indexed at all. This is bad news, as your primary key is looking for a date, which is then compared to CHANGE_RELEASE_DATE. If the column is not indexed, the database must read the table to get its values.

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

ATTID , ? , , . ATTID CHANGE_ID OLD_VALUE, , . CLASS, CHANGE_RELEASE_DATE OBJECT_ID.

, ( ), . , WHERE ( ) . OBJECT_ID ATTID , ,

  • INDEX SKIP SCAN, REDLINE_MSATTRIBUTE_INDEX1 CHANGE_ID
  • , CLASS CHANGE_RELEASE_DATE.

, , (CHANGE_RELEASE_DATE, CLASS, OBJECT_ID, ATTID). , , , .

+4

, , , . , , , .

, ?

+3

/ , , , 1) OPTIMIZER_MODE - , RBO. 2) - -. 3) , . -

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

4) , , (, OPTIMIZER_ INDEX_COST_ADJ ..)

+1

SELECT RELEASE_DATE FROM CHANGE WHERE ID = 136972355 ( , , ... - table, ....

1:

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

,    * 1 Date > Sysdate - Sysdate , . . , , .

, .

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;
0

More articles:


All Articles