-
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.
zm
-
when checked the dump trace file, found :
ORA-07445: exception encountered: core dump [evaopn2()+1104] [SIGSEGV] [Address not mapped to object] [0x0] [] []
zm
-
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
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.
zm
-
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
Sridhar R Patnam
-
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|