Home » Server Options » Text & interMedia » Oracle Text index CTXCAT not working with leading wild card
Oracle Text index CTXCAT not working with leading wild card [message #574906] Wed, 16 January 2013 13:06 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi Experts,

We are on Oracle 11.2.0.2 on Solaris 10 and I have implemented Oracle Text as in the example below:

CREATE TABLE TEST_USER
(
  FIRST_NAME  VARCHAR2(64 CHAR)                 NOT NULL,
  LAST_NAME   VARCHAR2(64 CHAR)                 NOT NULL
);
 
 
CREATE INDEX TEST_USER_IDX3 ON TEST_USER
(FIRST_NAME)
INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS('LEXER cust_lexer');
 
 
CREATE INDEX TEST_USER_IDX4 ON TEST_USER
(LAST_NAME)
INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS('LEXER cust_lexer');
 
Dont worry about the cust_lexer, it is for diacritical search and it is not relevant to this question so I am not copying the code for the preference I created etc.
Now I have a row of data in the table with first_name column as Supervisor. If I run the below sql, it gives output:
 
SELECT *
  FROM test_user
 WHERE catsearch (first_name, 'Supervisor', NULL) > 0;
 
FIRST_NAME                     LAST_NAME
------------------------------ ------------------------------
Supervisor                     upervisor
 
--even the below sql with wild card (*) at the end works fine...
SQL> SELECT *
  2    FROM test_user
  3   WHERE catsearch (first_name, 'Super*', NULL) > 0;
 
FIRST_NAME                     LAST_NAME
------------------------------ ------------------------------
Supervisor                     upervisor
 
 
However the below sql queries doesn't give any output, though they should return the same row as above!
 
SQL> SELECT *
  2    FROM test_user
  3   WHERE catsearch (first_name, '*visor', NULL) > 0;
 
no rows selected
 
 
SQL> SELECT *
  2    FROM test_user
  3   WHERE catsearch (first_name, '*vis*', NULL) > 0;
 
no rows selected



I already implemented this and now facing this issue that the queries are not working as expected - can someone suggest what can be done for this issue?

Thanks


Re: Oracle Text index CTXCAT not working with leading wild card [message #574908 is a reply to message #574906] Wed, 16 January 2013 13:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>--even the below sql with wild card (*) at the end works fine.
the asterisk ("*") is NOT a wild card character; percent ("%") & underscore ("_") are valid SQL wild card characters!
Re: Oracle Text index CTXCAT not working with leading wild card [message #574909 is a reply to message #574906] Wed, 16 January 2013 13:17 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I usually find that the indexes are unusable and I rebuild them with the following script.
ENWEBTT > @rebuild_context_indexes.sql

INSTANCE_N 'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
---------- -------------------------------------------------------------------
NWEBT      alter index ALFC.IDX_NFL_ABS_CONT_TITLE rebuild;
NWEBT      alter index CONTENT.TITLE_TEXT_IDX rebuild;
NWEBT      alter index SITE.PERSONS_TEXT_INDEX rebuild;
NWEBT      alter index XDB.XDB$ACL_XIDX rebuild;
NWEBT      alter index XDB.XDBHI_IDX rebuild;

5 rows selected.

ENWEBTT > list
  1   select INSTANCE_NAME,'alter index '||owner||'.'||index_name||' rebuild;'
  2  from dba_indexes,V$INSTANCE where index_type like '%DOMAIN%'
  3* order by owner
ENWEBTT > alter index ALFC.IDX_NFL_ABS_CONT_TITLE rebuild;

Index altered.

ENWEBTT > alter index CONTENT.TITLE_TEXT_IDX rebuild;

Index altered.

ENWEBTT > alter index SITE.PERSONS_TEXT_INDEX rebuild;

Index altered.

[Updated on: Wed, 16 January 2013 13:19]

Report message to a moderator

Re: Oracle Text index CTXCAT not working with leading wild card [message #574910 is a reply to message #574909] Wed, 16 January 2013 13:21 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
alan.kendall@nfl.com wrote on Thu, 17 January 2013 00:47
I usually find that the indexes are unusable and I rebuild them with the following script.

I can absolutely confirm that, invalid index is not the issue. Also here is something that cheered me up! I see one alternative way that works:
select * from test_user
where catsearch(first_name,
'<query> 
  <textquery grammar="context">
     %pervisor
  </textquery>
</query>','')>0

FIRST_NAME                     LAST_NAME
------------------------------ ------------------------------
Supervisor                     upervisor

SQL> select * from test_user
  2  where catsearch(first_name,
  3  '<query>
  4    <textquery grammar="context">
  5       %pervi%
  6    </textquery>
  7  </query>','')>0;

FIRST_NAME                     LAST_NAME
------------------------------ ------------------------------
Supervisor                     upervisor

This means I have to use this syntax to get around it!!!

Re: Oracle Text index CTXCAT not working with leading wild card [message #575572 is a reply to message #574910] Thu, 24 January 2013 05:08 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/message.jspa?messageID=10796926#10796926

In both forums, the question was posted in the wrong sub-forum. Please post Oracle Text questions in the Oracle Text sub-forums on both forums. I will move this post from Server Administration to Oracle Text.
Previous Topic: Re: Multi Column Datastore (spit from hijacked old thread)
Next Topic: Oracle Text CONTEXT index giving wrong results!!
Goto Forum:
  


Current Time: Thu Mar 28 03:43:37 CDT 2024