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

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Wed, 5 Oct 2011 20:21:17 -0400
Message-ID: <304F58144267C5439E733532ABC9A3A112F47D12_at_USA0300MS02.na.xerox.net>

Yes, stats were gathered and query_rewrite_enabled is set to true.  

From: Andrew Kerber [mailto:andrew.kerber_at_gmail.com] 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_at_xerox.com> 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

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






-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'


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

Received on Wed Oct 05 2011 - 19:21:17 CDT