玉面飞龙的BLOG

How to do “*partial_word*” search by Oracle Text. Please notice  grammar of context query template.

With a catsearch query operator, the wildcard is an asterisk (*), not a percent sign (%) as with a contains query operator. However, even with the correct wildcard, left truncation is not supported with ctxcat. However, if you use a query template, then you can use the context grammar with the % wildcard for context grammar and left truncation is supported. Please see the demonstration below that reproduces the problem, then corrects it.

SCOTT@10gXE> SELECT * FROM v$version

2  /

BANNER

Oracle Database 10g Express Edition Release 10.2.0.1.0 - ProductPL/SQL Release 10.2.0.1.0 - Production

CORE	10.2.0.1.0	Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SCOTT@10gXE> CREATE TABLE tmp_fulltext (fulltext VARCHAR2 (30))

2  /

Table created.

SCOTT@10gXE> INSERT ALL

2  INTO tmp_fulltext VALUES (’aabc’)

3  INTO tmp_fulltext VALUES (’abcd’)

4  SELECT * FROM DUAL

5  /

2 rows created.

SCOTT@10gXE> BEGIN

2    ctx_ddl.create_preference (’KEVFT_WL’, ‘BASIC_WORDLIST’);

3    ctx_ddl.set_attribute	 (’KEVFT_WL’, ’substring_index’,   ‘YES’);

4    ctx_ddl.set_attribute	 (’KEVFT_WL’, ‘prefix_index’,	   ‘YES’);

5    ctx_ddl.set_attribute	 (’KEVFT_WL’, ‘prefix_min_length’, 1);

6    ctx_ddl.set_attribute	 (’KEVFT_WL’, ‘prefix_max_length’, 6);

7  END;

8  /

PL/SQL procedure successfully completed.
SCOTT@10gXE> CREATE INDEX fulltext_idx ON tmp_fulltext (fulltext)

2  INDEXTYPE IS CTXSYS.CTXCAT

3  PARAMETERS (’WORDLIST KEVFT_WL’)

4  /

Index created.
SCOTT@10gXE> — reproduction of original problem using wrong wildcard for catsearch:

SCOTT@10gXE> SELECT * FROM TMP_FULLTEXT

2  WHERE CATSEARCH (fulltext, ‘%abc’, null) > 0

3  /

no rows selected
SCOTT@10gXE> SELECT * FROM TMP_FULLTEXT

2  WHERE CATSEARCH (fulltext, ‘abc%’, null) > 0

3  /

no rows selected
SCOTT@10gXE> — left truncation not supported, even with correct wildcard:
SCOTT@10gXE> SELECT * FROM TMP_FULLTEXT

2  WHERE CATSEARCH (fulltext, ‘*abc’, null) > 0

3  /
no rows selected
SCOTT@10gXE> SELECT * FROM TMP_FULLTEXT

2  WHERE CATSEARCH (fulltext, ‘abc*’, null) > 0

3  /

FULLTEXT

abcd

SCOTT@10gXE> — query using template with context grammar:
SCOTT@10gXE> SELECT * FROM TMP_FULLTEXT

2  WHERE CATSEARCH

3  	     (fulltext,

4  	      ‘<query>

5  		 <textquery grammar=”context”>

6  		   %abc

7  		 </textquery>

8  	       <query>’,

9  	      null) > 0

10  /

FULLTEXT

aabc
SCOTT@10gXE>