index is not used after a table reorg
Application - SAP
Database - oracle 10.2.0.4.0
OS - Sun sparc 64 bit
The below sql statement was using the index jcds~0 but after a table reorg(using Quest's live reorg) and collecting stats using brconnect, it has started performing full table scans.
UNIQUE INDEX SAPSR3."JCDS~0" ON SAPSR3.JCDS
(MANDT, OBJNR, STAT, CHGNR)
SELECT "MANDT" , "OBJNR" , "STAT" , "CHGNR" , "USNAM" , "UDATE" , "UTIME" , "TCODE" , "CDTCODE" , "INACT" , "CHIND"
FROM "JCDS" WHERE "MANDT" = 100 AND "OBJNR" BETWEEN 'VB*' AND 'VB99999999999999999999' AND "INACT" = ' ';
When I explain that statement on oracle sqlplus level, it seems to be using the index. However, when run from SAP, it does a full table scan. Does that make sense?
I was able to reorg just the table/index on a staging env by retaining the existing stats. The select was still using the index, so that confirmed that the reorg is not causing the issue but the stats is telling the optimizer to change the execution plan.
So, I copied the stats that work into the problem database hoping it should use the index now but it was still doing full table scans when run from the application level.
Can someone help me with what else I can look at to resolve this mystery. I'm running out of options. Appreciate your help.