prog:sql_nls_comp_nls_sort_inguistic_search

Inhaltsverzeichnis

NLS_COMP und NLS_SORT für Linguistische Vergleiche und Case Ignore in der Oracle Datenbank
- Zusammenhang NLS_COMP und NLS_SORT
Probleme
NLSSORT
Das ß in UPPER Vergleichen
- Quelle:

NLS_COMP und NLS_SORT für Linguistische Vergleiche und Case Ignore in der Oracle Datenbank

10g/11g

Problemstellung:
Im MS SQLServer erfolgt die Wildcard Suche über ein Textfeld automatisch unabhängig von der Groß- und Kleinschreibung.

Lösung:
Ein ähnliche Verhalten kann auch in der Oracle DB mit dem beiden NLS Parametern NLS_COMP und NLS_SORT erreicht werden. Dazu muss der NLS_COMP Parameter auf den Wert LINGUISTIC gesetzt werden.

Je nach dem Parameter ob nun der Parameter NLS_SORT auf einen Wert mit <SPRACHE>_CI oder <SPRACHE>_AI gesetzt ist, wird dann (und nur dann!) ein „linguistisch“ Case in sensitiver und/oder Accent in sensitiver Vergleich durchgeführt. (siehe Tabelle nach dem SQL Beispiel)

Beispiel Einstellen der Session:

-- Aktuelle Einstellung überprüfen
COLUMN VALUE format a20
COLUMN parameter format a30
SELECT parameter,VALUE FROM nls_session_parameters WHERE parameter LIKE '%O%' ;
PARAMETER                      VALUE
------------------------------ --------------------
NLS_SORT                       BINARY
NLS_COMP                       GERMAN
....
 
 
-- Werte einstellen
ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=XGERMAN_AI;
 
-- Erneut prüfen 
 
SELECT parameter,VALUE FROM nls_session_parameters WHERE parameter LIKE '%O%' ;
PARAMETER                      VALUE
------------------------------ --------------------
NLS_SORT                       XGERMAN_CI
NLS_COMP                       LINGUISTIC

Die NLS Settings werden immer vom Client an die Datenbank Session vererbt! Zwar können die Einstellung auch DB global gesetzt werden, der Client (Java, .NEt, SQL*Plus) kann das aber jederzeit wieder überschreiben!

Test:

-- Testdaten
CREATE TABLE t (wert varchar2(32));
INSERT INTO t VALUES ('Gunther');
commit;
 
--
 
SELECT * FROM t WHERE wert = 'gunther';
 
Wert
-----
Gunther

Zusammenhang NLS_COMP und NLS_SORT

Der Default für NLS_COMP ist *binary*, d.h. keine linguistischer Vergleich.

Steht der Wert auf LINGUISTIC (ANSI alternativ nur für Rückwärtskompatibilität!) ist je nach eingestelltem Wert für NLS_SORT das folgende Verhalten zu beobachteten:

NLS_COMP =	LINGUISTIC
NLS_SORT	Beschreibung	Vergleich 'ABC'= 'abc'	Vergleich 'Pèr'= 'per'	> Vergleich 'Z'>'A'	Sortierreihenfolge von 1 2 3 a b c ö ü ä _ ! A B C ß
BINARY	Binärer Vergleich auf den ASCII Wert	false	false	false	! 1 2 3 A B C _ a b c ß ä ö ü
GERMAN	Deutsche Sortierung	false	false	true	! _ a A ä b B c C ö ß ü 1 2 3
XGERMAN	Deutsche Sortierung ß wird berücksichtigt!	false	false	true	! _ a A ä b B c C ö ß ü 1 2 3
XGERMAN_DIN	Deutsche Telefonbuch Norm	false	false	true	! _ A a ä B b C c ö ß ü 1 2 3
XGERMAN_CI	_CI = postfixes für case-insensitive	true	false	false	! _ a A ä b B C c ö ß ü 1 2 3
XGERMAN_AI	_AI = postfixes für accent-insensitive	true	true	true	! _ ä a A b B C c ö ß ü 1 2 3
GENERIC_M_CI	Multilingual nach ISO/IEC 14651:2011 case-insensitive	true	false	true	_ ! 1 2 3 a A ä b B C c ö ß ü
GENERIC_M_AI	Multilingual nach ISO/IEC 14651:2011 accent-insensitive	true	true	true	1 2 3 A a ä b B C c ö ß ü _ !
FRENCH_M_AI	Französisch	true	true	true	1 2 3 A a ä b B C c ö ß ü _ !

Die Kürzel im Detail:

Ohne ein _M ⇒ Monolingual Linguistic Sorts
mit einem _M ⇒ Multilingual LInguistic Sorts
Postfix _CI ⇒ case-insensitive
Postfix _AI ⇒ accent-insensitive und case-insensitive

siehe auch : http://docs.oracle.com/cd/E11882_01/server.112/e10729/applocaledata.htm#NLSPG593

Probleme

Wird mit den „linguistischen“ Einstellungen über NLS_COMP gearbeitet, wird die Where Bedingung des SQL Statements umgeschrieben und auf beiden Seiten des Vergleiches Funktionen eingesetzt.

Beispiel:

SYS@GPI-WORKGROUP\JUPITER>SELECT * FROM t WHERE wert = 'pippèrr';
 
WERT
----------
Pippèrr
 
 
Ausf³hrungsplan
----------------------------------------------------------
Plan hash VALUE: 1601196873
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER(NLSSORT("WERT",'nls_sort=''FRENCH_M_AI''')=HEXTORAW('
              023F021B023F023F024F02450245') )

Beachten Sie die Filter Bedingung:
Aus einem „harmlosen“ wert = 'pippèrr'
wird ein (NLSSORT(„WERT“,nls_sort=FRENCH_M_AI)=HEXTORAW('023F021B023F023F024F02450245') ) !

Damit wird kein Index mehr verwendet!

Alternativ kann auch ein linguistischer Index angelegt werden:

CREATE INDEX idx_t ON t (NLSSORT(wert, 'NLS_SORT=FRENCH_M_AI'));
 
 
SELECT * FROM t WHERE wert = 'pippèrr';
 
Ausf³hrungsplan
----------------------------------------------------------
Plan hash VALUE: 1594971208
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | ROWS  | Bytes | Cost (%CPU)| TIME     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     2 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |     2 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - access(NLSSORT("WERT",'nls_sort=''FRENCH_M_AI''')=HEXTORAW('023F021B023F023F024F02450245') )

NLSSORT

Mit der Funktion NLSSORT im order by kann auch die entschrechende Reichenfolge erzwungen werden (unabhängig von den NLS Settings in der Session!):

SQL> SELECT * FROM t ORDER BY nlssort(wert,'NLS_SORT=BINARY')
 
WERT           
---------------
! 1 2 3 A B C _ a b c ß ä ö ü 
 
15 ROWS selected.
 
SQL> SELECT * FROM t ORDER BY nlssort(wert,'NLS_SORT=XGERMAN_DIN')
 
WERT           
---------------
! _ A a ä B b C c ö ß ü 1 2 3 
 
15 ROWS selected.
 
SQL> SELECT * FROM t ORDER BY nlssort(wert,'NLS_SORT=XGERMAN_AI')
 
WERT           
---------------
! _ ä a A b B C c ö ß ü 1 2 3 
 
15 ROWS selected.
 
SQL> SELECT * FROM t ORDER BY nlssort(wert,'NLS_SORT=GENERIC_M_CI')
 
WERT           
---------------
_ ! 1 2 3 a A ä b B C c ö ß ü 
 
15 ROWS selected.

(Werte zur Lesbarkeit nach rechts geschrieben .-) !)

Das ß in UPPER Vergleichen

Soll das ß in einem UPPER zu einem doppel s umgewandelt werden muss als NLS Setting XGERMAN für NLS_UPPER eingesetzt werden!

Beispiel:

ß wird nicht gewandelt

SELECT *  FROM DUAL WHERE UPPER ('größe') = 'GRÖSSE';
 
no ROWS selected.
 
 
SELECT UPPER ('größe') FROM DUAL
 
UPPER('GRÖßE')
--------------
GRÖßE

ß wird nicht gewandelt

SELECT *  FROM DUAL  WHERE NLS_UPPER ('größe', 'NLS_SORT=GERMAN') = 'GRÖSSE';
 
no ROWS selected.
 
 
SELECT NLS_UPPER ('größe', 'NLS_SORT=GERMAN') FROM DUAL
 
 
NLS_UPPER('GRÖßE','NLS_SORT=GERMAN')
------------------------------------
GRÖßE

ß wird zu SS gewandet durch den XGERMAN NLS Parameter!

SELECT *  FROM DUAL WHERE NLS_UPPER ('größe', 'NLS_SORT=XGERMAN') = 'GRÖSSE'
 
DUMMY
-----
X    
 
1 ROW selected.
 
 
SELECT NLS_UPPER ('größe', 'NLS_SORT=XGERMAN') FROM DUAL
 
NLS_UPPER('GRÖßE','NLS_SORT=XGERMAN')
-------------------------------------
GRÖSSE

Quelle:

Support:

Linguistic Sorting - Frequently Asked Questions(Doc ID 227335.1)