function based index problem

I created a function based index by using:

create unique index UQ_TABLE1_NAME on TABLE1 ( upper(NAME) );

Then tried to run a simple query with SQLPLUS:

SELECT * FROM table1 WHERE UPPER(name) LIKE UPPER('%abcd%') ORDER BY UPPER(name);

I got a error:

ORA-03113: end-of-file on communication channel

The strange thing is running "SELECT * FROM table1 WHERE name LIKE UPPER('%abcd%') ORDER BY UPPER(name)" or "SELECT * FROM table1 WHERE UPPER(name) LIKE UPPER('%abcd%') ORDER BY name" OK. I also removed the index and re_created it, then the problem disappeared.

Does any one konw how this happens.

Thanks.

when checked the dump trace file, found :

ORA-07445: exception encountered: core dump [evaopn2()+1104] [SIGSEGV] [Address not mapped to object] [0x0] [] []

Did u retry the same old statement after u got connected.Try the same old command,i guess the problem was with the memory mapping to the corresponding object.

regards
anandkl

got same problem when I got reconnected. The strang thing is that if I used only ONE upper function in the query, either in WHERE or ORDER BY, didn't have any problem.

found on metalink :

fact: Oracle Server Enterprise Edtion 9.0.1.0.1
symptom: Errors selecting from a table
symptom: ORA-7445: exception encountered: core dump [evaopn2()+1104]
[SIGSEGV] [Address not mapped to object] [0X0] [] []
symptom: DBMS_STATS have been gathered for the table
symptom: Optimizer mode set to cost based
symptom: Column constraint of UPPER used in table
cause:
ORA-7445: [EVAOPN2()+1104] error reported when select
statement using cost based optimizer and statistics is also on the table

fix:

Fixed in RDBMS 9.2

Workarounds:
Use Rule based optimizer
or
Set parameter optimizer_features_enable=8.1.7
or
Set parameter compatible="8.1.7"
or
Set ALTER SESSION SET EVENTS '10195 trace name context forever,level 10';

There have been problems with function based indexed in both initial versions of 8.x and 9.x.

Refer to patch read files whether there is a fix for Function Indexes. 9.2 should probably fix the problem

Analyse the tables statistics after creating the index and this may sort it out.

you need the following parameter to use function-based index:

query_rewrite_enabled = TRUE
query_rewrite_integrity = TRUSTED

Quote:

---

Originally posted by msassoon
Analyse the tables statistics after creating the index and this may sort it out.

---

DBMS_STATS allso has problems with FBI's -- might have to use the old ANALYZE statement there.