Oracle Text SQL Statements and Operators

CONTAINS

Use the CONTAINS operator in the WHERE clause of a SELECT statement to specify the query expression for a Text query.

CONTAINS returns a relevance score for every row selected. You obtain this score with the SCORE operator.

The grammar for this operator is called CONTEXT. You can also use CTXCAT grammar if your application works better with simpler syntax. To do so, use the Query Template Specification in the text_query parameter as described in this section.

Syntax

CONTAINS(
         [schema.]column,
         text_query    VARCHAR2
         [,label       NUMBER])
RETURN NUMBER;

[schema.]column

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

text_query

Specify one of the following:

• the query expression that defines your search in column.

• a marked-up document that specifies a query template. You can use one of the following templates:

Example

The following example searches for all documents in the in the text column that contain the word oracle. The score for each row is selected with the SCORE operator using a label of 1:

SELECT SCORE(1), title from newsindex 
    WHERE CONTAINS(text, 'oracle', 1) > 0;

The CONTAINS operator must be followed by an expression such as > 0, which specifies that the score value calculated must be greater than zero for the row to be selected.

When the SCORE operator is called (for example, in a SELECT clause), the CONTAINS clause must reference the score label value as in the following example:

SELECT SCORE(1), title from newsindex 
     WHERE CONTAINS(text, 'oracle', 1) > 0 ORDER BY SCORE(1) DESC;

The following example specifies that the query be parsed using the CATSEARCH grammar:

SELECT id FROM test WHERE CONTAINS (text,
 '
   
      cheap pokemon
   
   
  ' ) > 0;

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
If the optimizer chooses to use the functional query invocation, your query will fail. The optimizer might choose functional invocation when your structured clause is highly selective.
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:

The following limitations apply to these operators:

• The left-hand side (the column name) must be a column named in at least one of the indexes of the index set.

• The left-hand side must be a plain column name. Functions and expressions are not allowed.

• The right-hand side must be composed of literal values. Functions, expressions, other columns, and subselects are not allowed.

• Multiple criteria can be combined with AND. OR is not supported.

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

And these expression 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

You specify a marked-up string that specifies a query template. You can specify one of the following templates:

• query rewrite, used to expand a query string into different versions

• progressive relaxation, used to progressively issue less restrictive versions of a query to increase recall

• alternate grammar, used to specify CONTAINS operators (See CONTEXT Query Grammar Examples)

• alternate language, used to specify alternate query language

• alternate scoring, used to specify alternate scoring algorithms

  
  

  See Also: The text_query parameter description for CONTAINS for more information about the syntax for these query templates.

  
  

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

• AND (to combine two or more clauses)

  
  

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

  
  

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

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

1. 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-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

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

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/195110/viewspace-761587/，如需轉載，請註明出處，否則將追究法律責任。