Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> NLS_COMP in 8i
Has anyone successfully implemented the new parameter called NLS_COMP?
I'm trying to perform linguistic comparison in a WHERE clause. Everything works fine when I use NLSSORT() function on both side of a comparison. I created a function based index using NLSSORT() on a column and explain plan shows that optimizer is using this index when there is NLSSORT() function used on that column in a where clause. However, I can not persuade the CBO to perform linguistic comparison (instead of ordinary binary comparison) when there is no NLSSORT() in a where clause.
As per documentation, the new NLS_COMP parameter is intended to achieve this - if it is set to 'ASCII' and there is a linguistic index available for the column involved in a where condition, the CBO should automatically use the linguistic comparison. But I can't achieve this. It keeps ignoring the linguistic index and it performs binary comparison!
And one more odd thing about NLS_COMP. If I set it to ASCII in the init file and restart the database, the NLS_INSTANCE_PARAMETERS shows the NLS_COMP correctly set to ASCII. But if I use
ALTER SESSION SET NLS_COMP=ASCII then NLS_SESSION_PARAMETERS shows the NLS_COMP as unset (the value is NULL)! Is this a bug?
TIA,
Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)