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.