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

Size: px

Start display at page:

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

あまめかみいしづ
7 years ago
Views:

1 Oracle Database

2 Oracle Database ISO/IEC INTERNATIONAL STRING ORDERING... 7 Oracle Database / GENERIC_BASELETTER Oracle Database 2 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

3 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

4 ß 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

5 : 7 : < : 7 : < : 17 : Oracle Database 5 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

6 3 4 Unicode (U8271) 4 6 (U6015) 3 4 (U962A) 3 8 (U90A6) 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

7 ISO/IEC INTERNATIONAL STRING ORDERING ISO delta Oracle Database Oracle Database 7 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

8 Oracle9i 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

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

10 1. ll ch 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 Unicode Oracle Database 10 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

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

12 ISO/IEC 14651: International String Ordering GENERIC_M GENERIC_M GENERIC_M 1000 Unicode Oracle GENERIC_M ISO _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 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

13 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

14 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

15 Ä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

16 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

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

18 DB_BLOCK_SIZE ORA-1450 maximum key length exceeded 2K 758 4K K K 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 Oracle Database 18 Oracle Corporation Sorting Your Linguistic Data inside an Oracle Database

19 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

20 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

21 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

22 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 / _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

23 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

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

25 Oracle Database : Simon Law : Sergiusz Wolicki, Claire Ho, Barry Trute Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA U.S.A. : : : Oracle Copyright 2004 Oracle Corporation All rights reserved.

VRA1145-A

VRA1145-A DVL-99 GRAPHICS Æ L ; æ with REV FWD TITLE PREV AUDIO SUBTITLE CHP/TIME A/B ANGLE RETURN DISC SIDE OPEN/ CLOSE MODE DISPLAY JOG 6 PROGRAM REPEAT PAUSE STOP REV PLAY FWD STEP NEXT 7 3 FL DIMMER RANDOM