ORA-07445 with UTL_MATCH.JARO_WINKLER_SIMILARITY

I noticed an exception in the alert log of one of the databases with this error.

ORA-07445: exception encountered: core dump [pi_jaro_winkler_int()+409] [SIGSEGV] [Invalid permissions for mapped object] [0x2AD7A3481000] [] []

Looking in the trace file, an sql using this function was crushing the database:

UTL_MATCH.JARO_WINKLER_SIMILARITY

Because I found no suitable solution, I chose the following workaround:


A colleague found a suitable java class that had the same results like Oracle's UTL_MATCH's function and I created it in the database using the following syntax:


 CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED JaroWinklerDistance AS
....


The next step was to create a function that can call the java class and return the output.


CREATE OR REPLACE f_jaro(p_string1 varchar2 , p_string2 varchar2 ) RETURN number AS
LANGUAGE JAVA NAME 'JaroWinklerDistance.proximityPercent(java.lang.String , java.lang.String) return int';
/

 And the last step was to rewrite the sql so it would use this function.

how to optimize sqls with Like expression

Let's say we have an sql like this:

select * from table_andrei
where name like '%Andrei%' ;


We could rewrite it as it follows:


select * from table_andrei
where instr(name ,'Andrei')>0 ;



  An index on instr(name,’Andrei’) can be created only if it is absolutely necessary.