Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:sql_nls_comp_nls_sort_inguistic_search_sorting

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

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:

Cookies helfen bei der Bereitstellung von Inhalten. Diese Website verwendet Cookies. Mit der Nutzung der Website erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Computer gespeichert werden. Außerdem bestätigen Sie, dass Sie unsere Datenschutzerklärung gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website. Weitere Information
"Autor: Gunther Pipperr"
prog/sql_nls_comp_nls_sort_inguistic_search_sorting.txt · Zuletzt geändert: 2014/07/08 17:14 von gpipperr