Oracle Text Reference Release 9.0.1 Part Number A90121-01 |
|
SQL Statements and Operators, 3 of 8
Use the CATSEARCH operator to search CTXCAT indexes. Use this operator in the WHERE clause of a SELECT statement.
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.
CATSEARCH([schema.]column, text_query VARCHAR2, structured_query VARCHAR2,RETURN NUMBER;
Specify the text column to be searched on. This column must have a CTXCAT index associated with it.
Specify the query expression that defines your search in column
. The CATSEARCH operator supports only the following query operations:
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. |
(right and double truncated) |
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. |
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:
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');
The following statements create the CTXCAT index:
beginctx_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-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;
Syntax for CTXCAT Indextype in this chapter.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|