Oracle Context Option Application Developer's Guide | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
The following topics are covered in this chapter:
NAME | DESCRIPTION |
CONTAINS | Specifies the query expression and SCORE label for a one-step query |
HANDLE | Returns a handle for an operand |
SCORE | Returns the score generated by CONTAINS |
Attention: Before one-step queries can be executed, the database in which the text resides must be "text enabled" by setting the ConText Option initialization parameter TEXT_ENABLE = TRUE. This can be done in two ways:
For more information about using the ALTER SESSION command, see Oracle7 Server SQL Reference.
CONTAINS(COLUMN_ID, TEXT_QUERY, LABEL, POL_HINT)
Argument | Datatype | Default | Purpose |
COLUMN_ ID | NUMBER | Specifies the text column to be searched in the table. | |
TEXT_ QUERY | VARCHAR2 | Defines the text to be found in the specified database. (See "Query Expressions" for format and syntax of queries) | |
LABEL | NUMBER | Defines the label that identifies the score generated by the CONTAINS function (required only if CONTAINS called more than once in a query) | |
POL_HINT | VARCHAR2 | Specifies which policy to use for text columns that have multiple policies. | |
If only one CONTAINS function is used in a SELECT statement, the LABEL parameter is not required in the CONTAINS function; however, a SCORE label value of zero (0) is automatically generated. When the SCORE function is call (e.g. in a SELECT clause), the function must reference the label value.
The CONTAINS function may only be used in the WHERE clause of a SELECT statement; it may not be issued in the WHERE clause of an UPDATE, INSERT or DELETE statement.
In order to specify POL_HINT, you must specify LABEL as a place holder. POL_HINT must name a policy that is indexed either by text or theme. Do not specify USER.POLICY_NAME notation for POL_HINT; specify only policy name, otherwise ConText will raise an error. You cannot specify bind variables for POL_HINT.
When you do not specify POL_HINT and COLUMN_ID has more than one indexed policy attached to it, ConText uses the policy whose name is lexicographically first. For example, if a text column had policies named POL1 and POL2 associated with it and you did not specify POL_HINT, ConText uses POL1.
Suggestion: Oracle Corporation does not recommend relying on ConText to select a policy when you perform queries on columns with multiple policies. In this situation, always specify a policy name in POL_HINT.
HANDLE(CID, KEY)
Argument | Datatype | Default | Purpose |
CID | NUMBER | Column identifier. | |
IKEY | NUMBER | Value of the indexing key | |
HANDLE is a reserved word in a query and cannot be aliased.
HANDLE values are abstract.
SCORE(LABEL)
Argument | Datatype | Default | Purpose |
LABEL | NUMBER | Identifies the scores produced by a query. | |
The value specified for LABEL is the same value defined by the LABEL argument in the CONTAINS function that generated the scores and is referenced by the SCORE function in all other clauses.
If only one CONTAINS function is used in a SELECT statement, the LABEL parameter is not required in the CONTAINS clause, but a SCORE label value of zero (0) will be generated. All other clauses must then refer to SCORE(0) or SCORE(*).
SELECT SCORE(10), title FROM documents
WHERE CONTAINS(text, 'moriarity', 10) > 0
ORDER BY SCORE(10);
This example returns the score and title of all articles (documents) in the DOCUMENTS.TEXT column that contain the word moriarity, sorted by score.
The following syntax illustrates how the CONTAINS and SCORE query functions can be used in a SELECT statement.
SELECT SCORE(label1), SCORE(label2), ...SCORE(labeln), column1, column2, ... columnn FROM table[@dblink] WHERE CONTAINS (column_id, 'text_query', label1, pol_hint1) > 0 CONTAINS (column_id, 'text_query', label2, pol_hint2) > 0 ... CONTAINS (column_id, 'text_query', labeln, pol_hintn) > 0 ORDER BY SCORE(labeln)
Where:
labelx
is a numeric label that identifies the specific CONTAINS function that generated the score (required only when CONTAINS is called more than once in a query).
columnn
are the columns to be returned by the query. Each CONTAINS clause produces a virtual SCORE column that can be referenced by its numeric label (labelx) and included in the query output.
table
is the name of the table that contains the text column to be searched.
If a database link has been created for a remote database, the table specified in a one-step query can reside in the remote database. The table name must include the database link (@dblink) to access the remote table.
For more information about database links and remote queries, see Oracle7 Server Concepts.
column_id
identifies the column of the database that contains the text.
text_query
is the query expression to be used to return the relevant text.
pol_hintx
is the policy to be used when column_id has multiple policies.
Note: Other comparison operators and other numeric values can be used to satisfy this requirement and select rows with specific SCORE values; however, this method of refining the selection criteria is significantly less efficient than using the THRESHOLD and WEIGHT query expression operators.
SELECT employee_name, SCORE(10), SCORE(20) FROM employee_database WHERE CONTAINS (emp.resume, 'trumpet', 10) > 0 OR CONTAINS (emp.history, 'orchestra', 20) > 0 ORDER BY NVL(SCORE(10),0), NVL(SCORE(20),0);
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |