Shulou(Shulou.com)06/01 Report--

In sql sorting, oracle defaults to binary sorting. There are different values in uppercase and lowercase, with uppercase values at the top. Sometimes, what we need to deal with is that we want to sort by ignoring case. There are several ways to do this:

Set the NLS environment variable

Alter session set NLS_SORT = 'BINARY_CI'

Using the UPPER and LOWER functions

Use the UPPER function and the LOWER function to convert the field names and text to uppercase or lowercase and then compare them. The disadvantage of this approach is that after using the function, the standard index can no longer be used, the optimizer does not work properly, and the way to deal with it is to use function-based index (function-based index).

Note: NLS_SORT only affects the result of the sort, not other case operations. To solve the case-insensitive comparison operation, we can also do this by setting the NLS environment variable:

Alter session set NLS_COMP = 'LINGUISTIC'

There is a paragraph about NLS_SORT and NLS_COMP in the official document:

NLS_SORT specifies the collating sequence for ORDER BY queries.

If the value is BINARY, then the collating sequence for ORDER BY queries is based on the numeric value of

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

If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not

All) languages supported by the NLS_LANGUAGE parameter also support a linguistic sort with the same name.

Setting NLS_SORT to anything other 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 are built according to a binary order of keys.

Thus the optimizer can use an index to satisfy the ORDER BY clause when NLS_SORT is 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 gaze in the red section above, if NLS_SORT is not set to "Binary", it will cause a full table scan and the index will not be used. In our system, the data involved in the change order are tables with large data, and the efficiency of the query will be affected if the index is not used.

NLS_COMP specifies the collation behavior of the database session.

Values:

BINARY

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

LINGUISTIC

Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_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 should set NLS_COMP to LINGUISTIC.

Depending on the red section, to improve performance, you can create a linguistic index on the columns that need to be compared. If you want the NLS_COMP parameter value to take effect as LINGUISTIC, you need to set NLS_SORT to LINGUISTIC sort.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.