-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|