Re: Odd behavior of function-based index after DB upgrade

    I'm wondering whether the problem isn't partially coming from the BATCH_STATUS side. Typically a low-cardinality column with a huge disparity in the number of values. Would probably be better to list the values it can take rather than the values you don't want, if the list isn't too big, because in the long term hopefully most rows will have a PAID status. You said in another message that stats have been gathered; but have they been gathered in the same way, histograms, etc? It may be the combination of computed selectivities that goes awry.

I'd be curious to see the full piece of PL/SQL which harbors this query, especially to see whence :B2 is coming and what is done with the LEGACY_VENDOR_NO afterward.

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>



On 10/06/2011 01:24 AM, Hameed, Amir wrote:


> We have recently upgraded one of our Oracle ERP (11i.10.2) systems

> database from 10.2.0.4 to 11.1.0.7. After the upgrade, we are seeing

> some odd behavior on a custom job that was working fine prior to the

> upgrade. Prior to the upgrade, the job was using a function-based index,

> however, it has stopped using that index after the upgrade.

>

[snip]


>

>

> Below is one of the statements that are supposed to use this index:

>

> SELECT LEGACY_VENDOR_NO

>

> FROM TXRIOH0_INV_OUTBOUND_HDRS_ALL

>

> WHERE LPAD(NVL(VOUCHER_NO,0),7,'0') = :B2 AND NVL(SYSTEM,1) = NVL(:B1

> ,1) AND

>

> BATCH_STATUS NOT IN ('CONFIRMED','PAID','RECEIVED')

>

> ;

>

>

[snip]


> We re-ran statistics on the table but it did not help. The optimizer

> kept ignoring the index. I then set the optimizer mode to rule and the

> statement started using the index. I was able to create a test table and

> define a function-based index on it and it worked fine. So, I am not

> sure why the optimizer keeps ignoring this index. Has anyone run into a

> similar issue with 11g? There are lots of Oracle defined/standard

> function-based in the Oracle EBS database and they seemed to be working

> fine.

>

>

>

> Thanks

>

> Amir

>

>




--
http://www.freelists.org/webpage/oracle-l