Home » Server Options » Text & interMedia » Oracle Text CONTEXT index giving wrong results!! (Oracle 11.2.0.2 on Solaris 10)
Oracle Text CONTEXT index giving wrong results!! [message #575457] Wed, 23 January 2013 07:46 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Oracle Text Experts,

I am on Oracle 11.2.0.2 on Solaris 10 and have implemented Oracle Text functionality using CONTEXT type of index and I am getting some issues in it. I have a need to search with wild card like % and that gives wrong output. Following is the scenario where I am getting wrong results:
--creating preferences etc 
exec   ctxsys.ctx_ddl.create_preference ('cust_lexer', 'BASIC_LEXER'); 
exec   ctxsys.ctx_ddl.set_attribute ('cust_lexer', 'base_letter', 'YES'); -- removes diacritics 
exec   ctxsys.ctx_ddl.create_preference ('cust_wl', 'BASIC_WORDLIST') 
exec   ctxsys.ctx_ddl.set_attribute ;    ('cust_wl', 'SUBSTRING_INDEX', 'true'); 
 
--table set up 
CREATE TABLE TEST_USER 
( 
  FULL_NAME  VARCHAR2(64 CHAR)                  NOT NULL, 
  LAST_NAME  VARCHAR2(64 CHAR)                  NOT NULL 
); 
 
 
CREATE INDEX TEST_USER_IDX5 ON TEST_USER 
(FULL_NAME) 
INDEXTYPE IS CTXSYS.CONTEXT 
PARAMETERS('LEXER cust_lexer WORDLIST cust_wl SYNC (ON COMMIT)'); 
 
--data set up 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('Supervisor upervisor', 'upervisor'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('JOSEPH WILSON', 'WILSON'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('CHRISTOPHER Phil', 'TAYLOR'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('PAUL HERNANDEZ', 'HERNANDEZ'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('Betty Jipes', 'Jones'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('One Anna', 'Anna'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('Three Anna', 'Anna'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('Two Anna', 'Anna'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('Four Anna', 'Anna'); 
Insert into TEST_USER 
   (FULL_NAME, LAST_NAME) 
Values 
   ('Gary Barrow', 'Barrow'); 
COMMIT; 
 
--query using CONTAINS which gives WRONG output - because out intention is to return only those strings that START WITH the letter P but it returns another string that has P not at the starting!! 
 
SQL> select full_name from test_user where contains(full_name,'P%')>0; 
 
FULL_NAME 
---------------------------------------------------------------- 
PAUL HERNANDEZ 
CHRISTOPHER Phil 
 
-- I need output as follows in which the second value is not returned...meaning 'CHRISTOPHER Phil' should NOT be returned by the CONTAINS clause just as the LIKE operator below doesn't return it. 
SQL> select full_name from test_user where full_name like 'P%'; 
 
FULL_NAME 
---------------------------------------------------------------- 
PAUL HERNANDEZ


Can someone please suggest what needs to be done for this purpose?

Thanks,
Nirav_Hyd


Re: Oracle Text CONTEXT index giving wrong results!! [message #575461 is a reply to message #575457] Wed, 23 January 2013 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
query using CONTAINS which gives WRONG output - because out intention is to return only those strings that START WITH the letter P but it returns another string that has P not at the starting!!


Your understanding is wrong,
What you wrote is the full name which contain a word starting with P which is what the query returns.

Quote:
Can someone please suggest what needs to be done for this purpose?


Use LIKE as you did.

Regards
Michel
Re: Oracle Text CONTEXT index giving wrong results!! [message #575573 is a reply to message #575457] Thu, 24 January 2013 05:11 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
This question was answered on the OTN forums:

https://forums.oracle.com/forums/thread.jspa?threadID=2489805&tstart=0

In the future, please post Oracle Text questions in the Oracle Text sub-forum. I will move this post from Server Administration to Oracle Text.
Previous Topic: Oracle Text index CTXCAT not working with leading wild card
Next Topic: Problem with use of ORDSYS.ORDImage.importFrom(....)
Goto Forum:
  


Current Time: Thu Mar 28 04:50:55 CDT 2024