Gunther Oracle APEX und Datenbank Wiki

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
prog:sql_nls_comp_nls_sort_inguistic_search_sorting

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
prog:sql_nls_comp_nls_sort_inguistic_search_sorting [2013/03/18 22:18] – [Das ß in upper/lower Vergleichen] gpipperrprog:sql_nls_comp_nls_sort_inguistic_search_sorting [2014/07/08 17:14] (aktuell) – [Quelle:] gpipperr
Zeile 1: Zeile 1:
 +=====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**:
 +
 +<code sql>
 +
 +-- 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
 +
 +</code>
 +
 +<note important>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!</note>
 +
 +
 +**Test:**
 +
 +<code sql>
 +
 +-- Testdaten
 +create table t (wert varchar2(32));
 +insert into t values ('Gunther');
 +commit;
 +
 +--
 +
 +select * from t where wert = 'gunther';
 +
 +Wert
 +-----
 +Gunther
 +
 +
 +</code>
 +
 +
 +==== 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  [[http://www.iso.org/iso/home/store/catalogue_ics/catalogue_detail_ics.htm?csnumber=57976|ISO/IEC 14651:2011]]  \\ case-insensitive                                   | true  | false| true  | _ ! 1 2 3 a A ä b B C c ö ß ü  |
 +|GENERIC_M_AI | Multilingual nach  [[http://www.iso.org/iso/home/store/catalogue_ics/catalogue_detail_ics.htm?csnumber=57976|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:
 +<code sql>
 +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') )
 +              
 +</code>
 +
 +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 [[http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch5lingsort.htm#NLSPG294|linguistischer Index]] angelegt werden:
 +
 +<code sql>
 +
 +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') )
 +
 +</code>
 +
 +=====  NLSSORT =====
 +
 +Mit der Funktion NLSSORT im order by kann auch die entschrechende Reichenfolge erzwungen werden (unabhängig von den NLS Settings in der Session!):
 +
 +<code sql>
 +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.
 +
 +
 +</code>
 +
 +(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 [[http://oracle.informatik.haw-hamburg.de/server.111/b28286/functions105.htm|NLS_UPPER]] eingesetzt werden!
 +
 +
 +Beispiel:
 +
 +**ß wird nicht gewandelt**
 +
 +<code sql>
 +
 +select *  from DUAL where UPPER ('größe') = 'GRÖSSE';
 +
 +no rows selected.
 +
 +
 +select UPPER ('größe') from DUAL
 +
 +UPPER('GRÖßE')
 +--------------
 +GRÖßE      
 +
 +</code>
 +
 +
 +**ß wird nicht gewandelt**
 +
 +<code sql>
 +
 +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   
 +                            
 +
 +</code>
 +
 +** ß wird zu SS gewandet durch den XGERMAN NLS Parameter!  **
 +   
 +<code sql>
 +
 +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                               
 +
 +
 +</code>
 +
 +
 +
 +
 +
 +
 +
 +====Quelle:====
 +  * http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams143.htm#REFRN10117
 +  * http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch5lingsort.htm
 +
 +Support:
 +
 +  * Linguistic Sorting - Frequently Asked Questions(Doc ID 227335.1)
 +
 +
  
prog/sql_nls_comp_nls_sort_inguistic_search_sorting.txt · Zuletzt geändert: 2014/07/08 17:14 von gpipperr