Tips and considerations for Oracle SQL Sorting and comparison (i)

In SQL sorting, Oracle defaults to a binary ordering method. The case has different values, and the uppercase values are in front. Sometimes, we need to deal with situations where we want to sort by ignoring the casing. There are several ways to accomplish this:

1. Setting the NLS environment variable

   Alter session Set Nls_sort = ' binary_ci ';

2. Using the upper and lower functions

   Use the upper function and the lower function to convert the field names and text you want to compare to uppercase or lowercase before comparing them. The disadvantage of this approach is that, after using the function, the standard index is no longer available and the optimizer does not work properly, in a way that uses a function-based index (function-based index).

Note: Nls_sort only affects the results of sorting and does not affect other casing operations. To resolve case-insensitive comparison operations, we can also do this by setting the NLS environment variable:

Alter session Set Nls_comp = ' LINGuistic ';

Official documents about Nls_sort and Nls_comp have such a passage:

NLS_SORT Specifies the collating sequence for ORDER by queries.

• If The value is a BINARY, then the collating sequence for ORDER by queries are based on the numeric value of

  Characters (a binary sort, requires less system overhead).

• if The value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (and not

• Setting Nls_sort to anything and than BINARY causes a SORT to use a full table scan, regardless of the path

  Chosen by the 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 plan.

• You must use the Nls_sort operator with comparison operations if you want the linguistic SORT behavior.

According to the above-mentioned red part of the gaze, if the nls_sort is not set to "Binary", then it will cause a full table scan, will not use the index, in our system is involved in the change of the data is a large table, if not used to the index, the efficiency of the query will be affected.

NLS_COMP Specifies the collation behavior of the database session.

Values:

• BINARY

Normally, comparisons in the WHERE clause and in PL/SQL blocks are binary unless you specify the Nlssort function.

• Linguistic

                   comparisons for all SQL operations in the WHERE Clau SE and in PL/SQL blocks should use the linguistic sort                   &NBS P    specified in the Nls_sort parameter. 

• Ansi

A setting of ANSI is for backwards compatibility, and you should set Nls_comp to linguistic.

Depending on the red part, to improve performance you can create a linguistic index on the column that you want to compare. If you want to make the Nls_comp parameter value linguistic effective, you need to set Nls_sort to linguistic sort.

This article is from the "Dream Harbor" blog, please be sure to keep this source http://9785919.blog.51cto.com/9775919/1811587

Tips and considerations for Oracle SQL Sorting and comparison (i)