query is running very slow if the index hint is not forced
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: query is running very slow if the index hint is not forced

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    query is running very slow if the index hint is not forced

    All, The below query is running for ever. But i have index in all the columns which i used in where clause.

    SELECT COUNT(*)
    FROM material
    WHERE
    vbeln >= '0022000000' and vbeln <= '0022999999' and
    matnr = '000000000700203599'
    AND erdat >= TO_DATE ('01-JAN-2006', 'DD-MON-YYYY')
    AND erdat <= TO_DATE ('03-FEB-2006', 'DD-MON-YYYY')

    This below query takes only few min. Oracle has intellegence and oracle knows when to use index. But why the query is fast when we force the index.

    SELECT /*+ index(vbap,vbap_matnr_idx1) */ COUNT(*)
    FROM infsapb.vbap
    WHERE
    vbeln >= '0022000000' and vbeln <= '0022999999' and
    matnr = '000000000700203599'
    AND erdat >= TO_DATE ('01-JAN-2006', 'DD-MON-YYYY')
    AND erdat <= TO_DATE ('03-FEB-2006', 'DD-MON-YYYY')

    I use DBMS_STATS for analyze the query.
    Code:
     
    ORACLE> explain plan for 
      2  SELECT /*+ index(vbap,vbap_matnr_idx1) */ COUNT(*)
      3  FROM infsapb.vbap
      4  WHERE 
      5  vbeln >= '0022000000' and vbeln <= '0022999999' and
      6  matnr = '000000000700203599'
      7  AND erdat >= TO_DATE ('01-JAN-2006', 'DD-MON-YYYY')
      8  AND erdat <= TO_DATE ('03-FEB-2006', 'DD-MON-YYYY')
      9  /
    
    Explained.
    
    ORACLE> start explain_plan
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    
    ---------------------------------------------------------------------------------
    | Id  | Operation                    |  Name            | Rows  | Bytes | Cost  |
    ---------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                  |     1 |    38 |   283 |
    |   1 |  SORT AGGREGATE              |                  |     1 |    38 |       |
    |*  2 |   TABLE ACCESS BY INDEX ROWID| VBAP             |     1 |    38 |   283 |
    |*  3 |    INDEX RANGE SCAN          | VBAP_MATNR_IDX1  |  1251 |       |     9 |
    ---------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    ---------------------------------------------------
    
       2 - filter("VBAP"."VBELN">='0022000000' AND "VBAP"."VBELN"<='0022999999' AND
                  "VBAP"."ERDAT">=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
                  "VBAP"."ERDAT"<=TO_DATE('2006-02-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
       3 - access("VBAP"."MATNR"='000000000700203599')
    
    Note: cpu costing is off
    
    19 rows selected.
    
    
    ORACLE> explain plan for SELECT COUNT(*)
      2  FROM infsapb.vbap
      3  WHERE 
      4  vbeln >= '0022000000' and vbeln <= '0022999999' and
      5  matnr = '000000000700203599'
      6  AND erdat >= TO_DATE ('01-JAN-2006', 'DD-MON-YYYY')
      7  AND erdat <= TO_DATE ('03-FEB-2006', 'DD-MON-YYYY')
      8  /
    
    Explained.
    
    ORACLE> start explain_plan
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    
    ---------------------------------------------------------------------------------
    | Id  | Operation                    |  Name            | Rows  | Bytes | Cost  |
    ---------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                  |     1 |    38 |     7 |
    |   1 |  SORT AGGREGATE              |                  |     1 |    38 |       |
    |*  2 |   TABLE ACCESS BY INDEX ROWID| VBAP             |     1 |    38 |     7 |
    |*  3 |    INDEX RANGE SCAN          | VBAP_VBELN_IDX1  |    44 |       |     3 |
    ---------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    ---------------------------------------------------
    
       2 - filter("VBAP"."MATNR"='000000000700203599' AND
                  "VBAP"."ERDAT">=TO_DATE('2006-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
                  "VBAP"."ERDAT"<=TO_DATE('2006-02-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
       3 - access("VBAP"."VBELN">='0022000000' AND "VBAP"."VBELN"<='0022999999')
    
    Note: cpu costing is off
    
    19 rows selected.

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    is your column "VBAP"."MATNR" unique?
    did you define it as beeing unique (is the index VBAP_MATNR_IDX1 an unique index)?

    which command did you execute to analyze the table?

    which command did you execute to analyze the index?

  3. #3
    Join Date
    Dec 2005
    Posts
    195
    Mike,

    matnr is not a unique.

    Here is the analyze statment.

    DBMS_STATS.GATHER_TABLE_STATS ('quoteswp',p_tabname,'',estimate_percent=>10, degree=>4);

    I am not analying the index. Does it matter?.


    Quote Originally Posted by mike9
    is your column "VBAP"."MATNR" unique?
    did you define it as beeing unique (is the index VBAP_MATNR_IDX1 an unique index)?

    which command did you execute to analyze the table?

    which command did you execute to analyze the index?

  4. #4
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    How is the explain plan for the troublesome query?
    It uses the full table scan or other indexes on that table?

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    10053 trace will tell you why Oracle chose a particular access path.

    Can You post the trace here?

    Tamil

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Is the matnr column defined in the table as a numeric or a character data type?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    could you analyze the index an check if the optimzer choses then the correct index.
    To analyze the indexes you could simply set cascade=true

    DBMS_STATS.GATHER_TABLE_STATS ('quoteswp',p_tabname,'',estimate_percent=>10, degree=>4, cascade=>true
    );

  8. #8
    Join Date
    Mar 2002
    Posts
    534
    the reason why it choses the wrong index is because the optimizer thinks that he will get less rows returned with it

    Code:
    |*  3 |    INDEX RANGE SCAN          | VBAP_MATNR_IDX1  |  1251 |       |     9 |
     3 - access("VBAP"."MATNR"='000000000700203599')
    
    
    |*  3 |    INDEX RANGE SCAN          | VBAP_VBELN_IDX1  |    44 |       |     3 |
     3 - access("VBAP"."VBELN">='0022000000' AND "VBAP"."VBELN"<='0022999999')

  9. #9
    Join Date
    Mar 2002
    Posts
    534
    Quote Originally Posted by slimdave
    Is the matnr column defined in the table as a numeric or a character data type?
    based on the ouptut of the explain plan it seems to be a character data type.

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    add cascade=>true to the dbms_stats statement

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width