Oracle Database内での言語データのソート

Oracle Database 2003 9

Oracle Database... 3... 4... 4... 5 ISO/IEC 14651 INTERNATIONAL STRING ORDERING... 7 Oracle Database... 7... 8... 9... 10... 12... 17... 18 /... 19 GENERIC_BASELETTER... 20... 23... 24 Oracle Database 2 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

Oracle Database Mac Mc 1 1 a? B A C Oracle9i Database Oracle Database Oracle Database 3 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

ß 1 2 s ss ü ä ö ue ae oe 3 ch ll c l n cabalmente caballa cantina caña clamar curador chácara ch ll 3 æ ø å aa å ü y Zürich Aarfit Årbus Zyrardow 2 Èdit Edít Unicode ä b Oracle Database 4 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

26 40 4 0 3 : 7 : < : 7 : < : 17 : 2 2 2 Oracle Database 5 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

3 4 Unicode (U8271) 4 6 (U6015) 3 4 (U962A) 3 8 (U90A6) 3 7 2 2 SC TC SC TC (U738B) (U7389) (U6237) (U6236) (U9752) (U9751) 靚 (U9EC4) (U9EC3) (U4E00) (U4E8C) (U5F73) (U884C) (U6708) (U8089) Oracle Database 6 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

5 4 1 5 5 4 ISO/IEC 14651 INTERNATIONAL STRING ORDERING 1980 1992 ISO 14651 delta Oracle Database Oracle Database 7 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

Oracle9i 3 1. 2. 3. ASCII EBCDIC A Z ASCII EBCDIC ORDER BY C E b Ë â Oracle 5 UNICODE_ ASCII7 EBCDIC BIG5 GBK HKSCS AL16UTF16 US7ASCII WE8EBCDIC1047 ZHT16BIG5 ZHS16GBK ZHT16HKSCS Oracle Database 8 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

: Oracle6 2 a 10 5 A 10 10 ä 10 15 Ä 10 20 B 20 5 2 1 SQL NLSSORT RAW Oracle X Oracle 2 SPANISH XSPANISH Oracle Database 9 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

1. ll ch 1 2. 1 s' ' ss Oracle Database 10g ARABIC ARABIC_MATCH ARABIC_ABJ_SORT ARABIC_ABJ_MATCH AZERBAIJANI XAZERBAIJANI BENGALI BULGARIAN CANADIAN FRENCH CATALAN XCATALAN CROATIAN XCROATIAN CZECH XCZECH CZECH_PUNCTUATION XCZECH_PUNCTUATION DANISH XDANISH DUTCH XDUTCH EEC_EURO EEC_EUROPA3 ESTONIAN FINNISH FRENCH XFRENCH GERMAN XGERMAN GERMAN_DIN XGERMAN_DIN GREEK HEBREW HUNGARIAN XHUNGARIAN ICELANDIC INDONESIAN ITALIAN JAPANESE LATIN LATVIAN LITHUANIAN MALAY NORWEGIAN POLISH PUNCTUATION XPUNCTUATION ROMANIAN RUSSIAN SLOVAK XSLOVAK SLOVENIAN XSLOVENIAN SPANISH XSPANISH SWEDISH SWISS XSWISS THAI_DICTIONARY THAI_TELEPHONE TURKISH XTURKISH UKRAINIAN VIETNAMESE WEST_EUROPEAN XWEST_EUROPEAN ASCII EBCDIC 1 Oracle9i 1 Oracle ISO/IEC 14651 Unicode Oracle Database 10 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

2 3 1. 1 : 1 a b 1 - multi-lingual multilingual 2. 2 : Ö O Ö O O 1 2 2 3. 3 : 3 1 2 3 1 resume resumes 2 3 resume Resume résumé Résumé resumes 3 Oracle Database 3 Unicode 1 ä a Unicode Unicode 3.1 4 100 Unicode 10 Unicode 3.2 Oracle 110 1 Unicode Oracle Database 11 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

ISO/IEC 14651: International String Ordering GENERIC_M GENERIC_M GENERIC_M 1000 Unicode Oracle GENERIC_M ISO 14651 _M Oracle GENERIC_M CANADIAN_M DANISH_M FRENCH_M JAPANESE_M KOREAN_M SCHINESE_STROKE_M SCHINESE_PINYIN_M SCHINESE_RADICAL_M SPANISH_M TCHINESE_RADICAL_M TCHINESE_STROKE_M THAI_M Oracle XFRENCH FRENCH_M GENERIC_M ISO 14651 1 SQL NLS NLS NLS_SORT SQL Paramter type Syntax Default value String NLS SORT = { linguistic sort} Derived from NLS_LANGUAGE Oracle Database 12 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

Parameter scope Range of values Initialization Parameter, Environment Variable and ALTER SESSION or any valid linguistic sort definition name NLS_SORT NLS_LANGUAGE NLS_LANGUAGE NLS_SORT NLS_SORT ORDER BY 2 NLS_LANGUAGE Oracle NLS_SORT Oracle Database 10g NLS_LANGUAGE NLS_SORT AMERICAN ARABIC ASSAMESE AZERBAIJANI BANGLA BENGALI BRAZILIAN PORTUGUESE BULGARIAN CANADIAN FRENCH CATALAN CROATIAN CZECH DANISH DUTCH EGYPTIAN ENGLISH ESTONIAN FINNISH FRENCH GERMAN GERMAN DIN GREEK GUJARATI HEBREW HINDI HUNGARIAN ICELANDIC INDONESIAN ITALIAN ARABIC AZERBAIJANI BENGALI WEST_EUROPEAN BULGARIAN CANADIAN FRENCH CATALAN CROATIAN CZECH DANISH DUTCH ARABIC ESTONIAN FINNISH FRENCH GERMAN GERMAN GREEK HEBREW HUNGARIAN ICELANDIC INDONESIAN WEST_EUROPEAN Oracle Database 13 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

JAPANESE KANNADA KOREAN LATIN AMERICAN SPANISH LATVIAN LITHUANIAN MALAY MALAYALAM MARATHI MEXICAN SPANISH NORWEGIAN ORIYA POLISH PORTUGUESE PUNJABI ROMANIAN RUSSIAN SIMPLIFIED CHINESE SLOVAK SLOVENIAN SPANISH SWEDISH TAMIL TELUGU THAI TRADITIONAL CHINESE TURKISH UKRAINIAN VIETNAMESE SPANISH LATVIAN LITHUANIAN MALAY WEST_EUROPEAN NORWEGIAN POLISH WEST_EUROPEAN ROMANIAN RUSSIAN SLOVAK SLOVENIAN SPANISH SWEDISH THAI_DICTIONARY TURKISH UKRAINIAN VIETNAMESE Oracle NLS_LANG NLS_SORT NLS_SORT NLS_LANG <language> NLS_LANGUAGE NLS_SORT GENERIC_M 1. ALTER SESSION SET NLS_SORT=; SELECT product_name FROM product ORDER BY product_name; PRODUCT NAME ------------ Antenne Lcd aerial Oracle Database 14 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

Ähre ächzen 2. ALTER SESSION SET NLS_SORT= SWEDISH; SELECT product_name FROM product ORDER BY product_name; PRODUCT NAME ------------ aerial Antenne Lcd ächzen Ähre 3. GENERIC_M SELECT product_name FROM product ORDER BY NLSSORT (product_name, 'NLS_SORT=GENERIC_M'); PRODUCT NAME ------------ ächzen aerial Ähre Antenne Lcd SQL NLSSORT 4. ALTER SESSION SET NLS_SORT=GERMAN; SELECT product_name FROM product WHERE product_name > 'Antenne' ORDER BY product_name; PRODUCT NAME ------------ ächzen aerial ÄhLre Lcd Oracle Database 15 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

5. GERMAN SQL NLSSORT ALTER SESSION SET NLS_SORT=GERMAN; SELECT product_name FROM product WHERE NLSSORT (product_name) > NLSSORT ('Antenne') ORDER BY product_name; PRODUCT NAME ------------ Lcd SQL NLSSORT NLS_SORT NLS NLS_COMP Oracle8i Parameter type Syntax Default value Parameter scope Range of values String NLS_COMP = { ANSI} Initialization Parameter, Environment Variable and ALTER SESSION or ANSI 5. GERMAN ALTER SESSION SET NLS_SORT =GERMAN; ALTER SESSION SET NLS_COMP=ANSI; SELECT product_name FROM product WHERE product_name > 'Antenne' ORDER BY product_name; PRODUCT NAME ------------ Lcd : NLS_COMP NLS_SORT SQL WHERE ORDER BY START WITH IN/NOT IN BETWEEN CASE WHEN HAVING SQL NLSSORT Oracle Database 16 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

ORDER BY SELECT 3 1. CREATE INDEX french_index ON product (NLSSORT(product_name, 'NLS_SORT=FRENCH')); CREATE INDEX german_index ON product (NLSSORT(product_name, 'NLS_SORT=GERMAN')); NLS_SORT ORDER BY NLSSORT SQL NLS_SORT FRENCH french_index GERMAN german_index 2. GENERIC_M FRENCH_M ISO 14651 CREATE INDEX generic_index on product (NLSSORT(product_name, 'NLS_SORT=GENERIC_M'); NLS_SORT ORDER BY NLSSORT Oracle Database 17 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

DB_BLOCK_SIZE ORA-1450 maximum key length exceeded 2K 758 4K 1578 8K 3218 16K 6498 3. NLSSORT SQL LANG_COL NLS SORT NLS_SORT CREATE INDEX nls_index ON product (NLSSORT(product_name, 'NLS_SORT=' LANG_COL)); nls ORDER BY NLSSORT product_name, 'NLS_SORT=' LANG_COL CREATE INDEX german_index ON product (NLSSORT(product_name, 'NLS_SORT=GERMAN'), NLSSORT(company_name, 'NLS_SORT=GERMAN')); CREATE INDEX german_index ON product (model_number, NLSSORT(product_name, 'NLS_SORT=GERMAN'), NLSSORT(company_name, 'NLS_SORT=GERMAN')); 1 1. QUERY_REWRITE_ENABLED TRUE 2. QUERY_REWRITE_INTEGRITY=TRUSTED 3. COMPATIBLE 8.1.6 3 Oracle Database 18 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

4. NLS_SORT 4 NLS_SORT CREATE NLSSORT 2 5. FIRST_ROWS FIRST_ROWS Oracle 9i Database Release 2 WHERE WHERE NLSSORT(column_name) IS NOT NULL column_name ORDER BY column_name ORDER BY column_name NOT NULL WHERE Oracle9i Release 2 PRODUCT GENERIC_M NLS_GENERIC ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE; ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED; CREATE INDEX NLS_GENERIC ON product (NLSSORT(product_name, 'NLS_SORT=GENERIC_M')); ALTER SESSION SET NLS_SORT=GENERIC_M; ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS; SELECT * FROM product WHERE NLSSORT (product_name) IS NOT NULL - Not needed in Oracle9i Release 2 and onward ORDER BY product_name; /*+ index(table indexname) */ product_name NOT NULL WHERE Oracle9i Release 2 / SQL NLS_UPPER NLS_LOWER / Oracle Database 19 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

SELECT product_name FROM product WHERE NLS_UPPER(product_name, 'NLS_SORT = XGERMAN') = 'GROSSE'; PRODUCT NAME ------------ groβe Groβe GROSSE / CREATE INDEX case_insensitive_index ON product (NLS_UPPER(product_name)); NLS_UPPER () product_name SELECT * FROM PRODUCT WHERE NLS_UPPER(product_name) = 'GROSSE'; GENERIC_BASELETTER / SQL NLS_UPPER NLS_LOWER Oracle9i Release 2 GENERIC_BASELETTER GENERIC_BASELETTER / GENERIC_BASELETTER GENERIC_BASELETTER / ALTER SESSION SET NLS_COMP=ANSI; ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER; SELECT * FROM PRODUCT WHERE PRODUCT_NAME = 'database'; DATABASE Database database dätäbase Oracle Database 10g / Oracle Database / / SQL / LOWER/UPPER GENERIC_BASELETTER Oracle Database 20 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

LOWER/UPPER GENERIC_BASELETTER / SQL / / NLS_SORT / Oracle _CI / : Oracle _AI NLS_SORT = <NLS_sort_name>[_AI _cl] : NLS_SORT = JAPANESE_M /* / Japanese_M */ NLS_SORT = FRENCH_M_AI /* / French_M */NLS_SORT = XGERMAN_CI /* / XGerman */NLS_SORT = _CI /* / */ NLS_SORT = _AI /* / */ 1: / LETTER LETTER ----------- ä a A Z Oracle Database 21 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

NLS_SORT ALTER SESSION SET NLS_SORT=_CI; NLS_SORT _CI _AI 1) 2) _CI 3) _AI A a ä Z A a a Z A ä ä Z 1. 2. / _CI 3. / _AI ä a a z 2: / NLS_SORT GERMAN LETTER NLS_SORT GERMAN GERMAN_CI GERMAN_AI GERMAN GERMAN_CI GERMAN_AI a a ä A A a ä ä A Z Z Z 1. ä Z 2. / GERMAN_AI ä a Oracle Database 22 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

ISO Unicode Oracle9i GUI Oracle Locale Builder Oracle Locale Builder Oracle Locale Builder Oracle Locale Builder Oracle Database Oracle Database 23 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

Oracle Oracle ISO 14651 International String Ordering Unicode Collation Standard UCA Oracle 60 13 Oracle Oracle Locale Builder Oracle Locale Builder GUI Oracle Database 24 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

Oracle Database 2003 9 : Simon Law : Sergiusz Wolicki, Claire Ho, Barry Trute Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. : : +1.650.506.7000 : +1.650.506.7200 www.oracle.com Oracle Copyright 2004 Oracle Corporation All rights reserved.