CATSEARCH

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

The CATSEARCH operator also supports database links. You can identify a remote table or materialized view by appending @dblink to the end of its name. The dblink must be a complete or partial name for a database link to the database containing the remote table or materialized view. (Indexing of remote views is not supported.)

The grammar of this operator is called CTXCAT. You can also use the CONTEXT grammar if your search criteria require 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.

Note:

Both CTXCAT and the use of CTXCAT grammar as an alternative grammar for CONTEXT queries is deprecated. Instead, Oracle recommends that you use the CONTEXT indextype, which can provide all the same functionality, except that it is not transactional. Near-transactional behavior in CONTEXT can be achieved by using SYNC(ON COMMIT) or, preferably, SYNC(EVERY [time-period]) with a short time period.

CTXCAT was introduced when indexes were typically a few megabytes in size. Modern, large indexes, can be difficult to manage with CTXCAT. The addition of index sets to CTXCAT can be achieved more effectively by the use of FILTER BY and ORDER BY columns, or SDATA, or both, in the CONTEXT indextype. CTXCAT is therefore rarely an appropriate choice. Oracle recommends that you choose the more efficient CONTEXT indextype.

About Performance

Use the CATSEARCH operator with a CTXCAT index mainly to improve mixed-query performance. Specify your text query condition with text_query and your structured condition with the structured_query argument.

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

Limitations

If the optimizer chooses to use the functional query invocation, then your query will fail. The optimizer might choose functional invocation when your structured clause is highly selective.

You can use the INDEX hint to specify the optimizer to use the index and avoid functional evaluation of CATSEARCH.

The structured_query argument of the CATSEARCH operator must reference columns used during CREATE INDEX sets; otherwise, error DRG-10845 will be raised. For example, the error will be raised if you issue a CATSEARCH query on a view created on top of a table with the CTXCAT index on it, and the name of the logical column on the view is different from the actual column name on the physical table. The columns referenced by the structured_query argument of the CATSEARCH operator must be the physical column name used during CREATE INDEX sets, not the logical column on the view.

Syntax

CATSEARCH(
[schema.]column,
text_query [VARCHAR2|CLOB],
structured_query VARCHAR2,
RETURN NUMBER;

[schema.]column

Specifies 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

The CATSEARCH operator supports only the following query operations:

Table 1-7 provides the syntax for these operators.

Table 7 CATSEARCH Query Operators

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. Set prefix indexing with the BASIC_WORDLIST preference.

The following limitations apply to these operators:

For example, these expressions are supported:

catsearch(text, 'dog', 'foo > 15')
catsearch(text, 'dog', 'bar = ''SMITH''')
catsearch(text, 'dog', 'foo between 1 and 15')
catsearch(text, 'dog', 'foo = 1 and abc = 123')
catsearch@remote(text, 'dog', 'foo = 1 and abc = 123')

These expressions are not supported:

catsearch(text, 'dog', 'upper(bar) = ''A''')
catsearch(text, 'dog', 'bar LIKE ''A%''')
catsearch(text, 'dog', 'foo = abc')
catsearch(text, 'dog', 'foo = 1 or abc = 3')

Query Template Specification

Specifies a marked-up string that specifies a query template. Specify one of the following templates:

structured_query

Specifies 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 the CTX_DDL.ADD_INDEX package.

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

Examples

  1. 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 statements insert the values into the table:

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

  2. 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 CTXSYS.CTXCAT
PARAMETERS ('index set auction_iset');

  3. 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-12
          7 Nikon digital camera 22-FEB-12

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

    SELECT * FROM auction WHERE CATSEARCH(title, '"Sony DVD Player"',
'bid_close=''20-FEB-00''')> 0;

CATEGORY_ID TITLE                BID_CLOSE

----------- -------------------- ---------
          1 Sony DVD Player       20-FEB-12

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

    SELECT * FROM auction WHERE CATSEARCH(title, 'Sony DVD Player',
'order by bid_close
desc')> 0;
CATEGORY_ID TITLE                BID_CLOSE

----------- -------------------- ---------
          4 Sony DVD Player       25-FEB-12
          2 Sony DVD Player       24-FEB-12
          1 Sony DVD Player       20-FEB-12

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

    SELECT * FROM auction WHERE CATSEARCH(title, 'DVD - Player', 'order by bid_close
desc')> 0;

CATEGORY_ID TITLE                BID_CLOSE

----------- -------------------- ---------
          6 Tascam CD Burner     25-FEB-12

    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-12
          4 Sony DVD Player      25-FEB-12
          6 Tascam CD Burner     25-FEB-12
          2 Sony DVD Player      24-FEB-12
          5 Bose Speaker         22-FEB-12
          1 Sony DVD Player      20-FEB-12

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

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

CONTEXT Query Grammar Examples

The following examples show how to specify the CONTEXT grammar in CATSEARCH queries using the template feature:

PROMPT
PROMPT fuzzy: query = ?test
PROMPT should match all fuzzy variations of test (for example, text)
select pk||' ==> '||text from test
where catsearch(text,
'<query>
  <textquery grammar="context">
     ?test
  </textquery>
</query>','')>0
order by pk;

PROMPT
PROMPT fuzzy: query = !sail
PROMPT should match all soundex variations of bot (for example, sell)
select pk||' ==> '||text from test
where catsearch(text,
'<query>
  <textquery grammar="context">
     !sail
  </textquery>
</query>','')>0
order by pk;

PROMPT
PROMPT theme (ABOUT) query
PROMPT query: about(California)
select pk||' ==> '||text from test
where catsearch(text,
'<query>
  <textquery grammar="context">
     about(California)
  </textquery>
</query>','')>0
order by pk;

The following example shows a field section search against a CTXCAT index using CONTEXT grammar by means of a query template in a CATSEARCH query:

-- Create and populate table
create table BOOKS (ID number, INFO varchar2(200), PUBDATE DATE);

insert into BOOKS values(1, '<author>NOAM CHOMSKY</author><subject>CIVIL
   RIGHTS</subject><language>ENGLISH</language><publisher>MIT
  PRESS</publisher>', '01-NOV-2003');

insert into BOOKS values(2, '<author>NICANOR PARRA</author><subject>POEMS
  AND ANTIPOEMS</subject><language>SPANISH</language>
  <publisher>VASQUEZ</publisher>', '01-JAN-2001');

insert into BOOKS values(1, '<author>LUC SANTE</author><subject>XML
  DATABASE</subject><language>FRENCH</language><publisher>FREE
  PRESS</publisher>', '15-MAY-2002');

commit;

-- Create index set and section group
exec ctx_ddl.create_index_set('BOOK_INDEX_SET');
exec ctx_ddl.add_index('BOOKSET','PUBDATE');

exec ctx_ddl.create_section_group('BOOK_SECTION_GROUP',
      'BASIC_SECTION_GROUP');
exec ctx_ddl.add_field_section('BOOK_SECTION_GROUP','AUTHOR','AUTHOR');
exec ctx_ddl.add_field_section('BOOK_SECTION_GROUP','SUBJECT','SUBJECT');
exec ctx_ddl.add_field_section('BOOK_SECTION_GROUP','LANGUAGE','LANGUAGE');
exec ctx_ddl.add_field_section('BOOK_SECTION_GROUP','PUBLISHER','PUBLISHER');

-- Create index
create index books_index on books(info) indextype is ctxsys.ctxcat
  parameters('index set book_index_set section group book_section_group');

-- Use the index
-- Note that: even though CTXCAT index can be created with field sections, it
-- cannot be accessed using CTXCAT grammar (default for CATSEARCH).
-- We need to use query template with CONTEXT grammar to access field
-- sections with CATSEARCH.

select  id, info from books
where catsearch(info,
'<query>
      <textquery grammar="context">
              NOAM within author and english within language
      </textquery>
 </query>',
'order by pubdate')>0;

“Syntax for CTXCAT Index Type” Oracle Text Application Developer’s Guide