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

Printable View

02-09-2006, 11:20 AM
pranavgovind

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.
02-09-2006, 12:07 PM
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?
02-09-2006, 12:22 PM
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?.

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?
02-09-2006, 12:26 PM
Lily_Liu_2004

How is the explain plan for the troublesome query?
It uses the full table scan or other indexes on that table?
02-09-2006, 12:28 PM
tamilselvan

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

Can You post the trace here?

Tamil
02-09-2006, 12:28 PM
slimdave

Is the matnr column defined in the table as a numeric or a character data type?
02-09-2006, 12:29 PM
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
);
02-09-2006, 12:36 PM
mike9

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')
02-09-2006, 12:43 PM
mike9

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.
02-09-2006, 12:46 PM
davey23uk

add cascade=>true to the dbms_stats statement
02-09-2006, 01:18 PM
pranavgovind

I will analyze the index and see.
Thanks.

Quote:

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
);
02-09-2006, 06:34 PM
pranavgovind

Mike, oracle should use the index when it returns less rows. Do you think so?.

I also analyzed the table with cascade=true. But still i got to force the index. Oracle choose the path based on the cost. Looks like some time it could go wrong... Seems like.

Tamil, I will find the trace file and i will take a look at it.

Thanks.

Quote:

Originally Posted by mike9

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')
02-09-2006, 09:42 PM
mike9

which oracle version are you using?

how many rows ( count(*) ) do you got if you are only using the condtition
"VBAP"."MATNR"='000000000700203599'

how many rows ( count(*) ) do you got if you are only using the condtition
("VBAP"."VBELN">='0022000000' AND "VBAP"."VBELN"<='0022999999'