There are a number of lexer attributes in Oracle Text that
affect the way accented characters are dealt with. We're going to look at each of these and consider the effects they have, alone and together.
Before we look into them, a quick aside on typing accented
characters if you don't have them natively on your keyboard. I'm not going to go too deep into character
sets and code pages, but if you're on a European Windows machine, you're
probably using the WIN1252 character set.
If I want to type one of the accented characters in that set,
in most Windows applications I can hold down the ALT key, then enter the
four-digit decimal code for that character on my numeric keypad. So to enter a
lower-case o-umlaut character ("o" with two dots above it: "ö"), I would hold down ALT and type 0246 on the
Let's start with perhaps the simplest - the BASE_LETTER
attribute. If I set this to
"true" then characters with accents in the text will effectively be
indexed as their base form.
So for example if I index the German word "schön"
(meaning beautiful in English) then the actual indexed token will be "schon". That means if the user searches for " schön"
or "schon" then the word will be found (since the query term is
processed in the same way).
That makes life much easier for people who don't have the
oh-umlaut character on their keyboard, and don't know the ALT trick mentioned
above (or don't want to have to look up the necessary character code).
So that's simple - might as well set that option on always,
right? Well, not necessarily. Because in
German, the word "schon" actually has a quite different meaning to
" schön", and German users wouldn't want to find the unaccented word
if they specifically meant to look for the accented word.
So the simple rule of thumb is this: If you think that most
of your users will be searching for words for which they have the correct
keyboard (for example German users searching German text) then it's best to set
BASE_LETTER to false. But if you think
users might want to search foreign words for which they do not have the correct
keyboard (for example English, or multi-national users searching German or
mixed text) then it's best to set BASE_LETTER to TRUE.
Now it starts to get a little more complex. Some languages - notably German again - allow
you to avoid the use of accented characters by spelling the words
differently. In German, an accented
character is replaced by the un-accented form of the same letter, followed by
an "e". So "shön"
could equally validly be spelled "shoen", and every German user would
recognise it as the same word. Equally
"Muenchen" (the city English speakers call Munich) would be
recognized as the same as "München".
So that we can treat these alternate spelling forms as
equivalent Oracle Text has the ALTERNATE_SPELLING attribute. When set to "german", Oracle Text
will look for the "alternate form" of "vowel + e" and index
both that and the accented form of the word. When processing a query, it will equally translate "oe" into
"ö", etc, thus ensuring that the word can always be found, regardless
of which of the alternate forms is searched for, and which is in the indexed
following closely might ask "why does it index both the alternate form
"shoen" and the accented form "shön"? Surely it would be sufficient to just index
the "shön" form? Well, mostly it would. But what happens if the word in question was
actually an English word in the middle of the German text, such as
"poet"? OK, it would be
indexed as "pöt" and anybody seaching for "poet" would
still find it (since the transformation is applied to the searchterm as
well). But what if they used a wildcard
and searched for "po%"? They
would still expect to find the term, but
if only the accented form was indexed, they wouldn't. Hence the reason we index both forms, just in
Combining ALTERNATE_SPELLING and BASE_LETTER
OK, so we want ALTERNATE_SPELLING set to "german"
for our German text. But we also know
that people with non-German keyboards are often going to search it. So we BASE_LETTER on as well. What happens
If the indexer comes across "schön",
ALTERNATE_SPELLING would normally index that without any change. But BASE_LETTER forces it to the unaccented
form, and "schon" is actually indexed. If the indexer comes across
"shoen", then ALTERNATE_SPELLING decides it should be indexed as both
"schoen" and "schön". But before the tokens are written to the index, BASE_LETTER is applied,
so the tokens "shoen" and "shon" are indexed.
That all works fine, and we can find either term by
searching for "shon", "shön" or "shoen". Great!
But (there's always a but) what happens if we index the
French word "Rouède" (the name of a town near the Spanish border)? "uè"
is not a candidate for ALTERNATE_SPELLING, so it is left alone. Then BASE_LETTER
is applied, and the word "Rouede" is written to the index. If the user searches for "Rouède"
then the query-time processing works the same, the search is converted to
"Rouede" and the query works fine. However, if the user searches for the base
letter form "Rouede", things don't go so well. This time ALTERNATE_SPELLING does get applied to the query term
(since the query processor has no way of knowing that the "e" character
should be accented) and the searchterm is converted to "Roüde". BASE_LETTER is then applied, and it looks for
"Roude" in the index. But the
indexed term is "Rouede", so
nothing is found.
To solve this problem, the OVERRIDE_BASE_LETTER attribute
If you set OVERRIDE_BASE_LETTER to "true", then ALTERNATE_SPELLING will "mask" BASE_LETTER. That means that if we meet accented characters in the text which have a alternate form (such as "ö"), we will index them in their original, accented form and also in their alternate form. If we meet them in their alternate form (eg Muenchen) we will index ONLY the alternate form and not transform them. Accented characters which do not have an alternate form (such as "è") have BASE_LETTER processing applied to them to transform them to their equivalent unaccented character. Then at query time, we apply only ALTERNATE_SPELLING to any appropriate accented search terms, and BASE_LETTER to all others.This has the positive effect that our previous example
"rouède" can be found, if searched for with or without the accent on
the "e" character.
It does have the negative effect that base letter searches
no longer work on German words - we can't search for "shon" anymore,
only "shön" or "shoen" will work. So OVERRIDE_BASE_LETTER makes sense if we
want to perform ALTERNATE_SPELLING on German (or other specified language)
words, and BASE_LETTER on all other languages.
Appendix: Test Script
This is the script I used to test the effects of the various
options. To avoid any issues with
character set translation, I used the UNISTR() function to create Unicode
characters for my accented characters. Note the German words are prefixed by two-letter codes "wa" -
with accent, "na" - no accent and "af" alternate form. That allowed me to distinguish in the index
between the index terms derived from "schön" and those derived from
'my_lexer', 'BASIC_LEXER' );
'BASE_LETTER', 'true' );
drop table tt;
create table tt(a1 number primary key,text varchar2(45));
-- town name
"Rouède", accent on the e
insert into tt values (1,'rou'||unistr('\00E8')||'de');
-- shön with accent
insert into tt values (2,'wash'||unistr('\00F6')||'n');
-- shon no accent
insert into tt values (3,'nashon');
-- muenchen alternate
insert into tt values (4,'afmuenchen');
select * from tt;
create index tta on tt(text) indextype is ctxsys.context
parameters ( 'lexer my_lexer' );
set feedback 2
select token_text, token_type from dr$tta$i;
PROMPT searching for the base letter form, without accent on
the first e
select * from tt where contains(text,'Rouede')>0;
PROMPT and with the accent
select * from tt where contains(text,'Rou'||unistr('\00E8')||'de') > 0;
--select * from tt where
--select * from tt where contains(text,'muenchen') > 0;
set echo on
--select * from tt where contains(text,'nashoen') > 0;
--select * from tt where contains(text,'nashon') > 0;
--select * from tt where contains(text,'na'||unistr('\00F6')||'n') > 0;
select * from tt where contains(text,'washon') > 0;
select * from tt where contains(text,'washoen') > 0;
select * from tt where contains(text,'wa'||unistr('\00F6')||'n') > 0;
set echo off
-- The following section shows how to see how the query has
been transformed - it shows
-- the actual words looked for in the index.
drop table test_explain;
create table test_explain(
index_name => 'tta',
sharelevel => 0,
explain_id => 'Test');
col explain_id for a10
col id for 99
col parent_id for 99
col operation for a10
col options for a10
col object_name for a20
col position for 99
select explain_id, id, parent_id, operation, options,
from test_explain order by id;