Oracle8 ConText Cartridge QuickStart
Release 2.0
A54627_01

2
Using Text Queries

This chapter provides a quick description of the setup tasks that must be performed to enable text queries with ConText. It also provides examples of the three methods for performing queries.

The following topics are covered in this chapter:

Text Query Task Map
Startup and Hot Upgrade
Text Queries

Note:

Before you can perform the QuickStart tasks described in this chapter, ConText must be installed and certain implementation tasks must be completed.

If the required installation and installation tasks have not been completed, see Chapter 4, "Implementing ConText".

Text Query Task Map

QuickStart Tasks

Perform the following tasks to set up a text column in a table, index the column, and perform text queries on the column:

Startup and Hot Upgrade

Text Queries

Startup and Hot Upgrade

The first two setup tasks for text queries are:

Start ConText Servers
Perform Hot Upgrade of Columns

Note:

These tasks can be performed in any order.

Start ConText Servers

To create text indexes for a column, one or more ConText servers must be running with the DDL (D) personality. In addition, to perform text queries, one or more ConText servers must be running with the Query (Q) personality.

Note:

ConText servers can only be started by the CTXSYS Oracle user provided by ConText.

You can start a ConText server by calling the ctxsrv executable (linguistics enabled) or the ctxsrvx executable (linguistics not enabled) from the command-line. You can also use the ctxctl command-line utility to start ConText servers.

For example, to start a ConText server with the required personalities from the command-line of your server machine, execute the following command:

        $ ctxsrvx -user ctxsys/ctxsys -personality DQ -log ctx.log &

Note:

This example is specific to UNIX-based operating systems. The executable names and command-line syntax for ConText servers may be different in Windows NT and other operating systems.

For information about the executable names/syntax for ConText servers in your operating system, see the Oracle8 installation documentation for your operating system.

In this example, linguistics is not enabled for the ConText server. As a result, the server can not process theme indexing requests, theme queries, or requests for Linguistic Services; however, the memory required for running the server is substantially less than the memory required for a linguistically-enabled server.

Also in this example, the password for the CTXSYS user is 'ctxsys'. The server is started as a background process on the server machine and all ConText information for the session is written to a file named ctx.log.

Note:

This example results in the password for the CTXSYS user being visible to all users on the server machine.

If, for security reasons, you require the CTXSYS password to be masked out, you can call the ctxsrv|ctxsrvx executable without specifying the -user argument. The system then prompts you to enter this user information as username/password.

An additional personality, DML (M), can be assigned to ConText servers. ConText servers with the DML personality automatically update the ConText index for a column when changes which affect the index are made to rows in the table for the column.

Because the DML personality is not required for QuickStart, it is not discussed in this manual.

Perform Hot Upgrade of Columns

Hot upgrade is the process of defining database columns as text columns. A text column is any table or view column for which a policy has been created.

A policy identifies the column used to store text, the text storage method, and the options that ConText uses to create a ConText index for the column. ConText supports creating column policies for text indexing and theme indexing.

To create a text indexing policy for a column, call the CTX_DDL.CREATE_POLICY procedure and specify the following required parameters:

policy name
fully qualified column name for the policy

For example:

        exec ctx_ddl.create_policy('ctx_docs','ctxdev.docs.text')

In this example, a text indexing policy named ctx_docs is created for the text column in the docs table owned by ctxdev.

The following default ConText indexing options are used to create ctx_docs:

direct data store (text stored in text column; each row in the table represents a separate, complete document)
no filter (plain, unformatted text)
BASIC LEXER (standard punctuation, continuation, and join characters for text indexing)
default stoplist (list of words that are not included in text index)
no wordlist (Soundex information is not generated during text indexing)
generic text indexing engine

If you want to specify different indexing options for a policy, you can specify the desired options, also known as preferences, when you call CREATE_POLICY.

Text Queries

A text query searches the text column(s) in the queried table(s) for specified terms (words and phrases) and returns all rows (i.e. documents) which contain occurrences of the terms.

In addition, a score is returned for each selected document. The score is based on the number of occurrences of the query terms in the document and represents the relevance of the document to the query.

ConText supports a wide range of boolean and expansion operators which can be applied to the terms in a text query to produce different results. In addition, a text query can include searches for structured data.

Before you can perform a text query, you must perform the following tasks:

You can then perform text queries using any of the supported query methods:

Create Text Indexes for Text Columns

To create a text index for a column, call the CREATE_INDEX stored procedure in the CTX_DDL PL/SQL package and specify the text indexing policy for the column.

For example:

        exec ctx_ddl.create_index('ctx_docs')

In this example, CREATE_INDEX is called in SQL*Plus to create a text index for the text column (ctxdev.docs.text) in the ctx_docs policy.

After a text index is created for a column, ConText servers with the Query personality can process text queries for the column.

Create Result Tables (Two-Step Queries Only)

If you want to perform two-step queries, you must create a result table which stores a list of the primary keys (textkeys) and scores for the documents that satisfy the search criteria you specify in the first step of the two-step query.

The result table can have any name; however, it must have the structure (column names and datatypes) specified in the following example:

create table ctx_temp (textkey varchar(64), score number, conid number);

In this example, a result table named ctx_temp is created in SQL*Plus. The textkey column stores the primary key for the documents and the score column stores the scores generated by the query.

The third column, conid, stores a number which identifies the results for each query. The conid column is used only when the result table is used to store the results for multiple queries.

Two-Step Query Example

In the first step of a two-step query, you call the CONTAINS stored procedure in the CTX_QUERY PL/SQL package to populate an existing result table.

In the second step, you query the result table to return a hitlist of the documents.

Note:

Because the result table does not store document details or the text of the document, if you want to create a hitlist that includes document details and/or the text of a document, you must perform a query that joins the original text table and the results table.

The following example illustrates a basic two-step query:

begin

  ctx_query.contains('ctx_docs','lotus|oracle','ctx_temp');
end;

select score,title from ctx_temp, docs

        where ctx_temp.textkey=docs.pk
order by score desc;

In this example, a search is performed on the text column (ctxdev.docs.text) in the ctx_docs policy to find all documents in which the term oracle or lotus occurs. The results of the search are stored in the ctx_temp results table.

Then, the ctx_temp and docs tables are joined in a query to create a hitlist which lists score and title for each document in which the terms oracle or lotus occurs.

One-Step Query Example

One-step queries use the ConText SQL function, CONTAINS, which is called directly in the WHERE clause of a SELECT statement.

In a one-step query, the CONTAINS stored procedure and result tables required for two-step queries, are not used.

Note:

Because SELECT statements operate on column and table names, the name of the text column is used in a one-step query, rather than the policy for the column.

The following example illustrates a one-step query that returns the same results as in "Two-Step Query Example":

        select score(1), pk, title from docs

        where contains(text, 'lotus | oracle', 1) > 0

order by score(1) desc;

In-Memory Query Example

In-memory queries can be performed using OPEN_CON, FETCH_HITS, and CLOSE_CON in the CTX_QUERY PL/SQL package.

OPEN_CON opens a CONTAINS cursor to a query buffer and executes a query. The results of the query are stored in the query buffer. FETCH_HIT retrieves the results, one hit at a time, and CLOSE_CON releases the CONTAINS cursor.

Note:

In-memory queries are generally faster than one-step and two-step queries for queries that return large hitlists. In addition, in-memory queries do not require the allocation of database tables for the results.

However, in-memory queries do not support queries for structured data. If you wish to query for structured data, the structured data query must be performed separately from the in-memory query and the results of the two queries must be joined.

The following example illustrates an in-memory query that returns the same results as in "Two-Step Query Example":

declare
   score  char(5);
   pk     char(5);
   title  char(40);
   curid  number;
begin

   curid := ctx_query.open_con(policy_name => 'ctx_docs',
                                 text_query => 'lotus|oracle',
                              score_sorted => true,

                              other_cols => 'title');

   while (ctx_query.fetch_hit(curid, pk, score, title)>0)
      loop 

         dbms_output.put_line(score||pk||substr(title,1,50));
      end loop;
   ctx_query.close_con(curid);
end;

In this example, score, pk, title, and curid are declared as variables that are used by CTX_QUERY.OPEN_CON and CTX_QUERY.FETCH_HIT.

The SCORE_SORTED argument for OPEN_CON specifies that the results of the query are stored in the buffer in descending order by score. The OTHER_COLS argument species that the title column from the queried table is returned along with score and pk in the query results.

FETCH_HITS retrieves score, pk, and title for each hit until the buffer is empty.