SQL learning uses order by to sort in a specified order or in a custom order

We usually need to provide customers with a custom ordering method based on the customer's needs, so we usually have a way to implement the SQL, and refer to more information as summarized below (imperfect and wrong to point out):

First, if we just want to sort the application in a program as follows, we simply set the sort at the SQL statement level:   

1. Sort by default for oracled:select * from table_name ORDER by Col_name (DESC|ASC);( The default is ascending or unordered for lifting only in numeric fields);

2. Sort by custom order:SELECT * FROM table_name ORDER by decode (col_name, ' value1 ', 1,value2 ', 2,value3 ', 3, value4 ', 4,... Valuen ', N);

< Span style= "LINE-HEIGHT:25.2000007629395PX; Color:rgb (255,0,0) "> second, if we just want to sort the application in a program as follows, we simply set the sort method at the SQL statement level ( generally not set before the Chinese default sorting method of our system is to sort by pinyin   &NBSP ): &NBSP;&NBSP;

1. Sort by Pinyin: SELECT * FROMtable_name ORDER by Nlssort (col_name, ' nls_sort=schinese_pinyin_m ');
2. Sort by Stroke: SELECT * FROM table_name ORDER by Nlssort (col_name, ' nls_sort=schinese_stroke_m ');
3. Sort by Radical: SELECT * FROM table_name ORDER by Nlssort (col_name, ' nls_sort=schinese_radical_m ');
Attention:However, the query speed is very slow in the case of large data volumes and needs to be further optimized, according to Oracle's official documentation, when Oracle indexes the Chinese column, 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 using the 3 special sorts for Chinese described above, Oracle is unable to use the index and will perform a full table scan.The workaround is to establish linguistic index on this column. For example: Create INDEX Nls_index on my_table (nlssort (name, ' Nls_sort = Schinese_pinyin_m '));

Quote Original: 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.

Thirdly, if we want to sort in a particular way throughout the session, we need to sort the default settings fields at the session level:
1. Press Pinyin: Alter session set nls_sort = Schinese_pinyin_m;
2. By stroke: Alter session set nls_sort = Schinese_stroke_m;
3. By the Radicals: Alter session set nls_sort = Nls_sort=schinese_radical_m;

Iv. If we need to do a specific sorting of the entire data, we need to modify the Oracle Server system parameters (which we typically use less in this way):  
1. The win system modifies the registration form Hklc\software\oracle\home0\nls_sort

2. Other modifications configuration set Nls_sort=schinese_radical_m export Nls_sort

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQL learning uses order by to sort in a specified order or to sort in a custom order