CATSEARCH

Use the CATSEARCH operator to search CTXCAT indexes. Use this operator in the WHERE clause of a SELECT statement.

The grammar of this operator is called CTXCAT. You can also use the CONTEXT grammar if your search criteria requires special functionality,
such as thesaurus, fuzzy matching, proximity searching or stemming. To utilize the CONTEXT grammar,
use the Query Template Specification in the text_query parameter as described in this section.

About Performance

You use the CATSEARCH operator with a CTXCAT index mainly to improve mixed query performance. You specify your text query condition with text_query and your structured condition with structured_query.

Internally, Oracle Text uses a combined b-tree index on text and structured columns to quickly produce results satisfying the query.

Limitation

This operator does not support functional invocation.

Syntax

CATSEARCH(
[schema.]column,
text_query       VARCHAR2,
structured_query VARCHAR2,

RETURN NUMBER; 

[schema.]column

Specify the text column to be searched on. This column must have a CTXCAT index associated with it.

text_query

Specify one of the following to define your search in column.

• CATSEARCH query operations
• Query Template Specification (for using CONTEXT grammar)

CATSEARCH query operations

The CATSEARCH operator supports only the following query operations:

• Logical AND
• Logical OR (|)
• Logical NOT (-)
• " " (quoted phrases)
• Wildcarding

These operators have the following syntax:

Operation	Syntax	Description of Operation
Logical AND	a b c	Returns rows that contain a, b and c.
Logical OR	a | b | c	Returns rows that contain a, b, or c.
Logical NOT	a - b	Returns rows that contain a and not b.
hyphen with no space	a-b	Hyphen treated as a regular character. For example, if the hyphen is defined as skipjoin, words such as web-site are treated as the single query term website. Likewise, if the hyphen is defined as a printjoin, words such as web-site are treated as web site in the CTXCAT query language.
" "	"a b c"	Returns rows that contain the phrase "a b c". For example, entering "Sony CD Player" means return all rows that contain this sequence of words.
( )	(A B) | C	Parentheses group operations. This query is equivalent to the CONTAINS query (A &B) | C.
wildcard (right and double truncated)	term* a*b	The wildcard character matches zero or more characters. For example, do* matches dog, and gl*s matches glass. Left truncation not supported. Note: Oracle recommends that you create a prefix index if your application uses wildcard searching. You set prefix indexing with the BASIC_WORDLIST preference.

Query Template Specification

You specify a marked-up string that specifies a query based on the CONTEXT grammar.
Use the following tags and attribute values which are case sensitive:

TAG	Description	Possible Values
<query> </query>	Signals that this query be interpreted as a query template.
<textquery> </textquery>	Specify the query string.
grammar=	Specify the grammar of the query.	CONTEXT CTXCAT
<score></score>	Specify the score preference
datatype=	Specify the type of number returned as score.	INTEGER FLOAT

structured_query

Specify the structured conditions and the ORDER BY clause.
There must exist an index for any column you specify. For example, if you specify 'category_id=1 order by bid_close',
you must have an index for 'category_id, bid_close' as specified with CTX_DDL.ADD_INDEX.

With structured_query, you can use standard SQL syntax with only the following operators:

• =
• <=
• >=
• >
• <
• IN
• BETWEEN

Note: You cannot use parentheses () in the structured_query parameter.

Examples

Create the Table

The following statement creates the table to be indexed.

CREATE TABLE auction (category_id number primary key, title varchar2(20),bid_close date);

The following table inserts the values into the table:

INSERT INTO auction values(1, 'Sony CD Player', '20-FEB-2000');
INSERT INTO auction values(2, 'Sony CD Player', '24-FEB-2000');
INSERT INTO auction values(3, 'Pioneer DVD Player', '25-FEB-2000');
INSERT INTO auction values(4, 'Sony CD Player', '25-FEB-2000');
INSERT INTO auction values(5, 'Bose Speaker', '22-FEB-2000');
INSERT INTO auction values(6, 'Tascam CD Burner', '25-FEB-2000');
INSERT INTO auction values(7, 'Nikon digital camera', '22-FEB-2000');
INSERT INTO auction values(8, 'Canon digital camera', '26-FEB-2000');

Create the CTXCAT Index

The following statements create the CTXCAT index:

begin
ctx_ddl.create_index_set('auction_iset');
ctx_ddl.add_index('auction_iset','bid_close');
end; 


CREATE INDEX auction_titlex ON auction(title) INDEXTYPE IS CTXCAT PARAMETERS ('index set auction_iset'); 

A typical query with CATSEARCH might include a structured clause as follows to 

find all rows that contain the word camera ordered by bid_close:

SELECT * FROM auction WHERE CATSEARCH(title, 'camera', 'order by bid_close
desc')> 0;
CATEGORY_ID TITLE                BID_CLOSE
----------- -------------------- ---------
8 Canon digital camera 26-FEB-00
7 Nikon digital camera 22-FEB-00

The following query finds all rows that contain the phrase Sony CD Player and that have a bid close date of February 20, 2000:

SELECT * FROM auction WHERE CATSEARCH(title, '"Sony CD Player"', 'bid_close=''20-FEB-00''')> 0;
CATEGORY_ID TITLE                BID_CLOSE
----------- -------------------- ---------
1 Sony CD Player       20-FEB-00

The following query finds all rows with the terms Sony and CD and Player:

SELECT * FROM auction WHERE CATSEARCH(title, 'Sony CD Player', 'order by bid_close desc')> 0;
CATEGORY_ID TITLE                BID_CLOSE
----------- -------------------- ---------
4 Sony CD Player       25-FEB-00
2 Sony CD Player       24-FEB-00
1 Sony CD Player       20-FEB-00

The following query finds all rows with the term CD and not Player:

SELECT * FROM auction WHERE CATSEARCH(title, 'CD - Player', 'order by bid_close
desc')> 0;
CATEGORY_ID TITLE                BID_CLOSE
----------- -------------------- ---------
6 Tascam CD Burner     25-FEB-00

The following query finds all rows with the terms CD or DVD or Speaker:

SELECT * FROM auction WHERE CATSEARCH(title, 'CD | DVD | Speaker', 'order by bid_close desc')> 0;
CATEGORY_ID TITLE                BID_CLOSE
----------- -------------------- ---------
3 Pioneer DVD Player   25-FEB-00
4 Sony CD Player       25-FEB-00
6 Tascam CD Burner     25-FEB-00
2 Sony CD Player       24-FEB-00
5 Bose Speaker         22-FEB-00
1 Sony CD Player       20-FEB-00

    --创建索引集

    ctx_ddl.create_index_set('CIRCLE_INDEX_SET');

    ctx_ddl.add_index('CIRCLE_INDEX_SET','CREATETIME');

    

    --创建停用词列表

    ctx_ddl.create_stoplist('TAG_STOPLIST', 'BASIC_STOPLIST');

    --添加停用词

    ctx_ddl.add_stopword('TAG_STOPLIST','游戏');

    --删除停用词

    ctx_ddl.remove_stopword('TAG_STOPLIST','游戏');

    --删除停用词列表

    ctx_ddl.drop_stoplist('TAG_STOPLIST');

   

   --查询ctx_stoplists和ctx_stopwords 视图

   SELECT * FROM ctx_stoplists;

   SELECT * FROM ctx_stopwords;

   --查看系统默认参数项

   SELECT * FROM ctx_parameters;

   --查看索引集视图

   SELECT * FROM ctx_index_sets
    --创建全文索引

    CREATE INDEX CTXCAT_CIRCLE_TAG ON CIRCLE(TAG) INDEXTYPE IS CTXSYS.CTXCAT;

    --带停用词创建索引

    create index CTXCAT_CIRCLE_TAG on CIRCLE(TAG) indextype is CTXSYS.CTXCAT

    parameters ('stoplist TAG_STOPLIST');

    --带索引集创建所以

    CREATE INDEX CTXCAT_CIRCLE_TAG ON CIRCLE(TAG) INDEXTYPE IS CTXSYS.CTXCAT 

    PARAMETERS ('index set CIRCLE_INDEX_SET'); 

    

    --删除索引

    DROP INDEX CTXCAT_CIRCLE_TAG;
   --重建索引

   ALTER INDEX CTXCAT_CIRCLE_TAG REBUILD 

      PARAMETERS ('REPLACE STOPLIST TAG_STOPLIST');
    

   select * from (select  row_.*, rownum rownum_ from(

        SELECT * FROM CIRCLE c

        WHERE CATSEARCH(TAG,'游戏|漫画|旅游','order by createtime DESC')>0

   ) row_  where rownum <= 10) where rownum_ >= 0