-
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.
-
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?
-
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?.
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?
-
How is the explain plan for the troublesome query?
It uses the full table scan or other indexes on that table?
-
10053 trace will tell you why Oracle chose a particular access path.
Can You post the trace here?
Tamil
-
Is the matnr column defined in the table as a numeric or a character data type?
-
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.
-
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
);
-
I will analyze the index and see.
Thanks.
Originally Posted by 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
);
-
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')
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
|