SQL Statements and Operators, 3 of 8

CATSEARCH

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

About Performance

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

However, internally, Oracle always evaluates the text_query parameter first and then applies the strucutured_query condition against the rows returned from text_query.

This operator does not support the reverse behavior, also known as functional invocation. In this method, Oracle first retrieves the rows that satisfy the structured_query condition and then applies the text_query condition. Because of this, some CATSEARCH queries might not perform optimally.

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 the query expression that defines your search in column. 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 gls* 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.

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

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');

Query the Table

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-2000''')> 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

The following query finds all rows that are about audio equipment:

SELECT * FROM auction WHERE CATSEARCH(title, 'ABOUT(audio equipment)', NULL)> 0;