index is not used after a table reorg

[PAVB]

yes indeed; like:

alter session set timed_statistics = true;
alter session set sql_trace = true;
set autotrace on explain
alter session set events='10046 trace name context forever, level 12';
++ EXECUTE SQL ++
alter session set sql_trace = false;
alter session set timed_statistics = false;

[hrishy]

Hi

I am thinking this could be due to histograms.

If you run the following on sqlplus you should have the full table scan back
with bind varaibles instead of hard coded literals.

explain plan for
SELECT MANDT , OBJNR , STAT , 
CHGNR , USNAM , UDATE , 
UTIME , 
TCODE , CDTCODE , 
INACT , CHIND
FROM JCDS 
WHERE MANDT = :A0 
AND OBJNR BETWEEN :A1 AND :A2 
AND INACT = :A3

select * from table(dbms_xplan.display)

You might want to try and delete the histograms on the table and collect stats using dbms_stats and see what happens.

regards

[Nugpot]

Hi,

There are no histograms on that table columns.

SELECT column_name, num_distinct, num_buckets, histogram FROM USER_TAB_COL_STATISTICS WHERE table_name = 'JCDS'

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
MANDT 1 1 NONE
OBJNR 18262124 1 NONE
STAT 406 1 NONE
CHGNR 670 1 NONE
USNAM 22186 1 NONE
UDATE 4065 1 NONE
UTIME 86400 1 NONE
TCODE 125 1 NONE
CDTCODE 271 1 NONE
INACT 2 1 NONE
CHIND 2 1 NONE

Regards
Jay

[hrishy]

Hi

Would you mind showing me the output of the statements that i have posted below.

explain plan for
SELECT MANDT , OBJNR , STAT , 
CHGNR , USNAM , UDATE , 
UTIME , 
TCODE , CDTCODE , 
INACT , CHIND
FROM JCDS 
WHERE MANDT = :A0 
AND OBJNR BETWEEN :A1 AND :A2 
AND INACT = :A3

select * from table(dbms_xplan.display)

and also the plan with this

SELECT /*+ gather_plan_statistics */ * FROM 
JCDS 
WHERE 
MANDT = 100 
AND OBJNR BETWEEN 'VB*' AND 'VB99999999999999999999' 
AND INACT = ' ';

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));