Database Reference
In-Depth Information
can be created directly on the virtual column, and the code can be made transparent to the definition. The following
example shows how to add, index, and use a virtual column that applies the
upper
function to the
c1
column:
SQL> ALTER TABLE t ADD (c1_upper AS (upper(c1)));
SQL> CREATE INDEX i_c1_upper ON t (c1_upper);
SQL> SELECT * FROM t WHERE c1_upper = 'SELDON';
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 4 |
|* 2 | INDEX RANGE SCAN | I_C1_UPPER | 4 | 4 |
--------------------------------------------------------------------
2 - access("C1_UPPER"='SELDON')
Even though the examples in this section are based on B-tree indexes, function-based bitmap indexes are
supported as well.
Linguistic Indexes
Per default, the database engine performs
binary comparisons
for the purpose of comparing character strings. With
them, characters are compared according to their binary value. Consequently, two character strings are considered
equal only when the numeric code of each corresponding character is identical.
The database engine is also able to perform
linguistic comparisons
. With these comparisons, the numeric code
of each character doesn't have to be identical in order to match. For example, it's possible to instruct the database
engine to consider equal lowercase and uppercase characters, or characters with and without accents. To manage
this behavior of the SQL operators, the
nls_comp
initialization parameter is available. It can be set to one of the
following values:
•
binary
: Binary comparisons are used. This is the default.
•
linguistic
: Linguistic comparisons are used. The
nls_sort
initialization parameter specifies
the linguistic sort sequence (and therefore the rules) that applies to the comparisons. The
accepted values for a specific version can be displayed with the following query:
SELECT value FROM v$nls_valid_values WHERE parameter = 'SORT'
ansi
: This value is available for backward compatibility only.
linguistic
should be used instead.
The dynamic
nls_comp
and
nls_sort
initialization parameters can be set at the instance and session levels.
At the session level, they can be set with the
ALTER SESSION
statement, as well as be defined on the client side at the
operating system level (for example, in Microsoft Windows with an entry in the registry). Be aware that having
client-side settings is the rule, not the exception. Therefore it's quite common to see server-side settings that are
overridden by client-side settings.
As an example, consider a table storing the following data (the table and the test queries are available in the
linguistic_index.sql
script):
•
SQL> SELECT c1 FROM t;
