Home » Server Options » Text & interMedia » Oracle Text - creating index case [b]in[/b]sensitive
Oracle Text - creating index case [b]in[/b]sensitive [message #134069] Tue, 23 August 2005 08:12 Go to next message
sebastianR
Messages: 33
Registered: August 2005
Member
Hi,

i am using Oracle Text for indexing now, because it speeds up embedded wildcard searches like %1234% enormously.

I therefor set the regarding preferences:
declare	
begin 
ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST'); 
ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH',3);
ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', 4);
ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
end;


And finally create my index.

-- DANGEROUS - needs MUCH time to execute (appr. 1hrs 40 mins) 
create index SR_TEST_IDX_4 on MLSEGMENT(DATA)
indextype is ctxsys.context parameters ('wordlist mywordlist MEMORY 50M');

Now I examined that using the index my search is case sensitive, which I do not want.

select data from
(select data from mlsegment where UPPER(DATA) LIKE UPPER('%trans%')
MINUS
(select data from mlsegment where contains(data, '%Trans%', 1) > 0
union
select data from mlsegment where contains(data, '%trans%', 1) > 0
union
select data from mlsegment where contains(data, '%TRANS%', 1) > 0))


This query still delivers 2 rows where the searchString is like 'tranSET' ...

Now I discovered that while I didn't specify, I am using the BASIC_LEXER which includes the case sensitivty.

My question now is, do I really have to write me my own USER_LEXER for that purpose?
(see link http://www.lc.leidenuniv.nl/awcourse/oracle/text.920/a96518/cdatadic.htm#50620)

That seems to be kinda complicated. Is there another way to disable case sensitivy?

I can't use the UPPER function (or any other) in the contains clause of my queries.

In other words, the BASIC_LEXER is totally what I need except that I'd like it case insensitive.

I appreciate you suggestions, thanks in advance,

Sebastian
Re: Oracle Text - creating index case [b]in[/b]sensitive [message #134076 is a reply to message #134069] Tue, 23 August 2005 08:25 Go to previous messageGo to next message
sebastianR
Messages: 33
Registered: August 2005
Member
I just figured out the possibility to do the conversion of the searchstring on the application side.
select data from mlsegment where contains(data, '%TRANS%', 1) > 0

But the problem remains that I can't UPPER() the data column in the contains statement. So the application-side conversion of '%trans%' into '%TRANS%' won't help me at all, right?

In addition I think that making a case insensitive index would maybe additionally pay off performance. Why making an index case sensitive if i only search for converted Upper SearchStrings?

SebastianR
Re: Oracle Text - creating index case [b]in[/b]sensitive [message #134091 is a reply to message #134069] Tue, 23 August 2005 09:51 Go to previous message
sebastianR
Messages: 33
Registered: August 2005
Member
I figured it out now, pretty easy.

begin
ctx_ddl.create_preference('mylex', 'BASIC_LEXER');
ctx_ddl.set_attribute('mylex', 'MIXED_CASE', 'NO');
end;


Just had to make a pref with value basic lexer and and attr with MIXED_CASE = NO.
The default is YES.

greets,
SebastianR
Previous Topic: end-of-file on communication channel while executing the query with CONTAINS
Next Topic: ORA-03113 with Oracle 8.1.7.4.0
Goto Forum:
  


Current Time: Thu Mar 28 11:37:20 CDT 2024