Our database has varchar-fields that contain umlauts (e.g. Möller). We are receiving material in which umlauts ä, ö and ü have been replaced with a, o and u, respectively. With a bit of googling I figured I could do an umlaut-insensitive query by setting nls_comp to LINQUISTIC and nls_sort to GERMAN_AI (have also tried XGERMAN_DIN_AI). These seem to work but the queries are hopelessly slow, especially when I have to do a join.
select customer.id, customer.name from customer
inner join address on address.id on customer.id
where customer.name like 'Moller'
and address.street like 'Hogberggatan';
With umlauts this query take 0.08 seconds. With nls_comp and nls_sort set, and without umlauts it takes over 2 minutes. We're using 11g and the chactersets are NLS_NCHAR_CHARACTERSET = AL16UTF16 and NLS_CHARACTERSET = WE8ISO8859P1.
Any ideas on how we could speed up the queries?