Oracle Sorting for Chinese

Prior to Oracle 9i, the sorting of Chinese was by default sorted by 2-binary encoding. Several new options were added at 9i:

1. Sort by Chinese pinyin: Schinese_pinyin_m
2. Sort by Chinese radicals: schinese_radical_m
3. Sort by Chinese strokes: schinese_stroke_m

While Oracle 9i is the sort of Chinese is by default by pinyin (not Nls_sort = Schinese_pinyin_m, but instead of the Chinese column when the SQL is not specified when ordering the default by pinyin), is different from the previous 2 encoding. The following are the specific uses:

1. Write directly in SQL, for example:
   1. SELECT * from TEAM ORDER by Nlssort (sort field name, ' Nls_sort = Schinese_pinyin_m ');
   2. SELECT * from TEAM ORDER by Nlssort (sort field name, ' Nls_sort = Schinese_stroke_m ');
   3. SELECT * from TEAM ORDER by Nlssort (sort field name, ' Nls_sort = Schinese_radical_m ');
2. Configuration in the initialization parameter nls_sort, which can be specified when the database is created, or can be modified by alter session. If the former, it takes effect in all sessions. For example:
   1. Use the SELECT * from Nls_session_parameters; statement to see the value of Nls_sort.
   2. Change configuration file: Alter system set nls_sort= ' Schinese_pinyin_m ' scope=spfile;
   3. Change Session:alter SESSION Set nls_sort = Schinese_pinyin_m;

Here's an extra look at performance issues, as Oracle's official documentation explains, when the Chinese column is indexed, it is sorted according to the 2 encoding, so if Nls_sort is set to binary, The sort can take advantage of the index. If it is not a 2 binary sort, but instead uses the 3 special sorts that are described above for Chinese, Oracle cannot use the index and will perform a full table scan. This is important to note, the use of plsql tools to compare performance efficiency. The solution is to Establish linguistic index on this column. For example: Create INDEX Nls_index on my_table (nlssort (name, ' nls_sort = Schinese_pinyin_m '));

The following is the original text in the Oracle documentation:

Note:
Setting Nls_sort to anything and than BINARY causes a SORT to use a full table scan, regardless of the path chosen by th E Optimizer. Binary is the exception because indexes was built according to a binary order of keys. Thus the optimizer can use a index to satisfy, the ORDER by clause when Nls_sort are set to BINARY. If Nls_sort is set to any linguistic sort, the optimizer must include a full table scan and a full SORT in the execution p Lan.

Oracle Sorting for Chinese