OraFAQ Forum: Text & interMedia » Oracle Text - creating index case [b]in[/b]sensitive

Home » Server Options » Text & interMedia » Oracle Text - creating index case [b]in[/b]sensitive

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Text - creating index case [b]in[/b]sensitive [message #134069]

Tue, 23 August 2005 08:12

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

Report message to a moderator

Re: Oracle Text - creating index case [b]in[/b]sensitive [message #134076 is a reply to message #134069]

Tue, 23 August 2005 08:25

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

Report message to a moderator

Re: Oracle Text - creating index case [b]in[/b]sensitive [message #134091 is a reply to message #134069]

Tue, 23 August 2005 09:51

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 11:35:03 CST 2024