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

[pranavgovind]

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.

 
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.

[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?

[pranavgovind]

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?.

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?

[Lily_Liu_2004]

How is the explain plan for the troublesome query?
It uses the full table scan or other indexes on that table?

[tamilselvan]

10053 trace will tell you why Oracle chose a particular access path.

Can You post the trace here?

Tamil

[slimdave]

Is the matnr column defined in the table as a numeric or a character data type?

[mike9]

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

[mike9]

the reason why it choses the wrong index is because the optimizer thinks that he will get less rows returned with it

|*  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')

[mike9]

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.

[davey23uk]

add cascade=>true to the dbms_stats statement