Oracle sorting by Chinese

Oracle sorts Chinese characters before oracle 9i by default. several new options are added for 9i: www.2cto.com sorted by Chinese pinyin: SCHINESE_PINYIN_M sorted by Chinese radicals: SCHINESE_RADICAL_M sorted by Chinese strokes: SCHINESE_STROKE_M while oracle 9i sorts Chinese in pinyin by default (not NLS_SORT = SCHINESE_PINYIN_M, but in Chinese by default when NLS_SORT is not specified in SQL) is different from the previous binary encoding sorting. the specific usage is as follows: directly written in SQL, for example: SELECT * FROM TEAM ORDER BY NLSSORT (sorting field name, 'nls _ SORT = SCHINESE_PINYIN_M '); SELECT * from team order by nlssort (sorting field name, 'nls _ SORT = SCHINESE_STROKE_M '); SELECT * from team order by nlssort (sorting field name, 'nls _ SORT = SCHINESE_RADICAL_M '); www.2cto.com is configured in the initialization parameter NLS_SORT, which can be specified during database creation or modified through alter session. if it is the former, it will take effect in all sessions. for example, use the select * from NLS_SESSION_PARAMETERS statement to view the value of NLS_SORT. change the configuration file: alter system set nls_sort = 'schinese _ PINYIN_M 'scope = spfile; change session: alter SESSION set NLS_SORT = SCHINESE_PINYIN_M; pay attention to performance issues, according to the explanation in the official oracle documents, oracle sorts Chinese columns according to BINARY encoding when creating an index. Therefore, if NLS_SORT is set to BINARY, indexes can be used for sorting. if the preceding three types of special sorting for Chinese are used instead of binary sorting, oracle cannot use indexes and performs full table scanning. note that the execution efficiency can be compared using plsql. the solution is to create a linguistic index on this column. for example, create index nls_index ON my_table (NLSSORT (name, 'nls _ SORT = SCHINESE_PINYIN_M '));