Yes, stats were gathered and query_rewrite_enabled is set to true. From: Andrew Kerber [mailto:andrew.kerber@xxxxxxxxx] Sent: Wednesday, October 05, 2011 8:09 PM To: Hameed, Amir Cc: oracle-l mailing list Subject: Re: Odd behavior of function-based index after DB upgrade Did you gather stats after the upgrade? Does query_rewrite_enabled=true? On Wed, Oct 5, 2011 at 6:24 PM, Hameed, Amir <Amir.Hameed@xxxxxxxxx> 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. select * from dba_ind_expressions where table_name='TXRIOH0_INV_OUTBOUND_HDRS_ALL' ; OWNER INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION ------ ------------------------------ ------------------------------ ---------------------------------------- --------------- XRX TXRIOH0_INV_OUTBOUND_HDRS_IDX3 TXRIOH0_INV_OUTBOUND_HDRS_ALL LPAD(NVL("VOUCHER_NO",'0'),7,'0') 1 XRX TXRIOH0_INV_OUTBOUND_HDRS_IDX3 TXRIOH0_INV_OUTBOUND_HDRS_ALL NVL("SYSTEM",'1') 2 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') ; The explain plan shows that the statement is doing FTS on the table: ------------------------------------------------------------------------ ------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------ ------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 0 |00:01:03.22 | 359K| 359K| | 1 | CONCATENATION | | 1 | | 0 |00:01:03.22 | 359K| 359K| |* 2 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | 0 | |* 3 | TABLE ACCESS FULL| TXRIOH0_INV_OUTBOUND_HDRS_ALL | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | |* 4 | FILTER | | 1 | | 0 |00:01:03.22 | 359K| 359K| |* 5 | TABLE ACCESS FULL| TXRIOH0_INV_OUTBOUND_HDRS_ALL | 1 | 1 | 0 |00:01:03.22 | 359K| 359K| ------------------------------------------------------------------------ ------------------------------------------------ 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 -- //www.freelists.org/webpage/oracle-l -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- //www.freelists.org/webpage/oracle-l