Benutzer-Werkzeuge

Webseiten-Werkzeuge

Action disabled: source

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
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:

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
prog/sql_nls_comp_nls_sort_inguistic_search_sorting.txt · Zuletzt geändert: 2014/07/08 17:14 von Gunther Pippèrr