CES supports four
different case styles for searching text properties, namely:
-
UPPER (U)
-
LOWER (L)
-
CASE-SENSITIVE (S)
-
CASE-INSENSITIVE (I)
When a text property is marked with
UPPER case, the property data is stored as upper case
in the database. Likewise, if a property is marked with LOWER case, then the data is stored as lower case. If
the case style is either CASE-SENSITIVE or
CASE-INSENSITIVE , the property data is stored as
is and no case transformation occurs during the data loading.
The case style is defined for each text
property in the attribute of
S_CLP_CASE_STYLE
of the Class Properties class (S_CLP_TABLE ).
However, when the data of a property is searched, the case style of the
property makes a lot of difference. If the case style is
UPPER, then the
user's search value is converted to upper case and is matched with the
database property data, as the following WHERE clause example shows:
WHERE property1 = UPPER (searchvalue')
If the case style is LOWER , then the
user's search value is converted to lower case and is matched with the
database property data, as the following WHERE clause example shows:
WHERE property1 = LOWER (searchvalue')
If the case style is CASE-SENSITIVE ,
then the user's search value is directly matched with the database property
data, as shown in the following WHERE clause example:
WHERE property1 = searchvalue'
If the case style is CASE-INSENSITIVE ,
then both the user's search value and the database property data are
converted to UPPER
case and matched with each other, as shown in the
following WHERE
clause example:
WHERE UPPER(property1) =
UPPER(searchvalue')
The first three cases
(UPPER ,
LOWER and
CASE-SENSITIVE )
do not pose any additional search performance
overheads; however, during a
CASE-INSENSITIVE
search, the UPPER function is applied to the search property.
Oracle turns off use of an index on a property when there is a function, an
arithmetic expression, or NULL
concatenation added to a property in the WHERE clause, even though there is an index available
for the property.
Some of the solutions recommended for the
CASE-INSENSITIVE style properties
are:
-
Convert the data to
UPPER case and store it in the database.
-
Train the users so that they know exactly how
to enter their search values. For example, they can enter "Software",
instead of "software".
-
For text properties, whose property length is
short and which have indexes built for search performance, create a
FUNCTION-BASED INDEX (FBI).
To do this, from
the EDM menu, select Tools > Index Management >
Function-Based Index.
|