The NLS_COMP and NLS_SORT parameters are sorted by binary by default, which is inappropriate for Chinese sorting, for example. Use these two parameters to specify the sorting method, such as pinyin or, which may cause performance problems. The solution is to use the NLSSORT function to create a function index. NLS_COMP {BINARY | LIN
The NLS_COMP and NLS_SORT parameters are sorted by binary by default, which is inappropriate for Chinese sorting, for example. Use these two parameters to specify the sorting method, such as pinyin or, which may cause performance problems. The solution is to use the NLSSORT function to create a function index. NLS_COMP = {BINARY | LIN
NLS_COMP and NLS_SORT Parameters
Oracle uses binary for sorting by default, which is inappropriate for Chinese sorting, for example.
Use these two parameters to specify the sorting method, such as pinyin or, which may cause performance problems.
The solution is to use the NLSSORT function to create a function index.
NLS_COMP = {BINARY | LINGUISTIC | ANSI}
-
BINARY
Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify theNLSSORT function.
LINGUISTIC
Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks shocould use the linguistic sort specified in theNLS_SORT parameter. to improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.
ANSI
A setting of ANSI is for backwards compatibility; in general, you shoshould setNLS_COMP to LINGUISTIC
NLS_SORT = {BINARY | linguistic_definition}
NLS_SORT specifies the collating sequence for character value comparison in varous SQL operators and clses, for example, ORDER BY, GROUP BY, comparison conditions (=, <>,<=, >= ), IN, BETWEEN, LIKE, MIN/MAX, GREATEST/LEAST, and INSTR.
If the value is BINARY, then comparison is based directly on byte values in the binary encoding of the character values being compared. the ordering depends on the character set of the compared values, which is either the database character set (for VARCHAR2, CHAR, LONG, and CLOB) or the national character set (for NVARCHAR2, NCHAR, and NCLOB ).
If the value is a named linguistic sort, then comparison is defined by this sort. A linguistic sort uses varous rules to achieve ordering expected by speakers of one or more natural ages. this is usually the same ordering that is used in dictionaries and/or telephone directories in those ages.
The BINARY comparison is faster and uses less resources than any linguistic comparison but for text in a natural language, it does not provide ordering expected by users.
The value of NLS_SORT affects execution plans of queries. because a standard index cannot be used as a source of values sorted in a linguistic order, an explicit sort operation must usually be med instead of an index range scan. A functional index on the NLSSORT function may be defined to provide values sorted in a linguistic order and reintroduce the index range scan to the execution plan.
Perform the following test:
First, let's take a look at the available Chinese sorting methods:
Select value from v $ nls_valid_values where parameter = 'sort 'and value like' % SCHINESE % ';
Return available Chinese sorting methods
SCHINESE_PINYIN_M -- sort by pinyin
SCHINESE_STROKE_M -- sort by strokes (first order) and heads (Second Order)
SCHINESE_RADICAL_M -- sort by the beginning (first order) and strokes (Second Order)
Next we will perform the test.
Create index emp_ename_idx on emp (ename );
Check the execution plan of select * from emp where ename = 'Mike,
Explain plan for select * from emp where ename = 'Mike ';
Select * from table (dbms_xplan.display );
Oracle will index emp_ename_idx.
1. Timeout .---------------------------------------------------------------------------------------------
2. | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | 3. lifecycle 4. | 0 | select statement | 1 | 46 | 2 (0) | 00:00:01 | 5. | 1 | table access by index rowid | EMP | 1 | 46 | 2 (0) | 00:00:01 | 6. | * 2 | index range scan | EMP_ENAME_IDX | 1 | 1 (0) | 00:00:01 | 7. 8.
9. Predicate Information (identified by operation id): 10. ----------------------------------------------------- 11.
12. 2-access ("ENAME" = u'mike ') alter session set nls_comp = 'linguistic ';
Alter session set nls_sort = 'schinese _ PINYIN_M ';
Then, view the execution plan. The result is as follows:
1 .--------------------------------------------------------------------------
2. | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | 3. -------------------------------------------------------------------------- 4. | 0 | select statement | 1 | 46 | 3 (0) | 00:00:01 | 5. | * 1 | table access full | EMP | 1 | 46 | 3 (0) | 00:00:01 | 6. -------------------------------------------------------------------------- 7.
8. Predicate Information (identified by operation id): 9. ----------------------------------------------------- 10.
11. 1-filter (NLSSORT ("ENAME", 'nls _ sort = ''schinese _ pinyin_m'') = HEXTORAW 12. ('0230021b022301fe0000020202020007020202 ') you can see that the full table is scanned.
Next, If you create the following index
Create index emp_ename_idx_nlssort on emp (nlssort (ename, 'nls _ SORT = SCHINESE_PINYIN_M '));
Check the execution plan and check the index emp_ename_idx_nlssort.
1. Timeout .-----------------------------------------------------------------------------------------------------
2. | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | 3. lifecycle 4. | 0 | select statement | 1 | 46 | 2 (0) | 00:00:01 | 5. | 1 | table access by index rowid | EMP | 1 | 46 | 2 (0) | 00:00:01 | 6. | * 2 | index range scan | EMP_ENAME_IDX_NLSSORT | 1 | 1 (0) | 00:00:01 | 7. 8.
9. Predicate Information (identified by operation id): 10. ----------------------------------------------------- 11.
12. 2-access (NLSSORT ("ENAME", 'nls _ sort = ''schinese _ pinyin_m'') = HEXTORAW ('0230021b022301fe000 13. 100 '))
