Stored Query Expressions

In pre-8.1 you can store the definition and results of a query. You can then use the SQE operator in a query expression to obtain the results. For queries such as wildcard queries, using stored query expression improves performance since results are stored.

In 8.1, the procedure CTX_QUERY.STORE_SQE stores only the definition of the query. No results are stored. Referencing the query with the SQE operator merely references the definition of the query. In this way, SQEs make it easy for defining long or often used query expressions.

Stored query expressions are not attached to an index. When you call CTX_QUERY.STORE_SQE, you specify only the name of the stored query expression and the query expression.

The pre-8.1 notion of a session SQE has gone away. The query definitions are stored in the Text data dictionary. Any user can reference a stored query expression.

See Also: To learn more about the syntax of CTX_QUERY.STORE_SQE, see the Oracle8i interMedia Text Reference.

The administrative procedures of REFRESH_SQE and PURGE_SQE are obsolete in interMedia Text 8.1.

Pre-8.1 Method

In pre-8.1, you define and use a stored query expression as follows:

1. Call CTX_QUERY.STORE_SQE to store the results for the text column or policy. With STORE_SQE, you specify a name for the SQE, a policy (which identifies the text column for the SQE), a query expression, and whether the SQE is a session or system SQE

2. Call the stored query expression in the query expression of a text (or theme) query. ConText returns the results of the SQE in the same way it returns the results of a regular query. If the results of the SQE are out-of-date, ConText automatically re-evaluates the SQE before returning the results.

Administration of stored query expressions can be performed using the REFRESH_SQE, REMOVE_SQE, and PURGE_SQE procedures in the CTX_QUERY PL/SQL package.

Pre-8.1 Example

To create a session SQE named PROG_LANG, use CTX_QUERY.STORE_SQE as follows:

exec ctx_query.store_sqe('emp_resumes', 'prog_lang', 'computer science', 
'session');

This SQE queries the text column for the EMP_RESUMES policy and returns all documents that contain the term computer science. It stores the results in the SQE table for the policy.

The prog_lang stored query expression can then be called within a query expression as follows:

select score, docid from emp 
where contains(resume, 'sqe(prog_lang)')>0 
order by score;

8.1 Method

You define and use a stored query expression as follows:

1. Call CTX_QUERY.STORE_SQE to store the results for the text column. With STORE_SQE, you specify a name for the SQE and a query expression. The session and system parameters have gone away.

2. Call the stored query expression in a query expression using the SQE operator. Oracle returns the results of the SQE in the same way it returns the results of a regular query. The query is evaluated at the time the SQE is called.

   The procedures REFRESH_SQE and PURGE_SQE are obsolete. You delete using REMOVE_SQE.

Example

The following example creates a stored query expression called disaster that searches for documents containing the words tornado, hurricane, or earthquake:

begin
ctx_query.store_sqe('disaster', 'tornado | hurricane | earthquake');
end;

To execute this query in an expression, write your query as follows:

SELECT SCORE(1), title from news 
   WHERE CONTAINS(text, 'SQE(disaster)', 1) > 0
   ORDER BY SCORE(1);

See Also: To learn more about the syntax of CTX_QUERY.STORE_SQE, see the Oracle8i interMedia Text Reference.