Oracle Context Option QuickStart

CHAPTER 2. Using Text Queries

Text Query Task Map
Startup and Hot Upgrade
Text Queries

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

The following topics are covered in the chapter:

Text Query Task Map

Startup and Hot Upgrade

Text Queries

Attention: Before you can perform the QuickStart tasks described in this chapter, ConText Option must be installed and certain implementation tasks must be completed. If the required installation and installation tasks have not been completed, see "Implementing ConText Option (Chapter 4)."

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

Start ConText Servers

Perform Hot Upgrade of Columns

Text Queries

Create Text Indexes for Text Columns

Create Results Tables (Two-Step Queries Only)

Two-Step Query Example

One-Step Query Example

In-Memory Query Example

Startup and Hot Upgrade

The first two setup tasks for text queries are:

start one or more ConText servers with the appropriate personality mask

prepare (Hot Upgrade) columns for text indexing

These tasks can be performed in any order.

Start ConText Servers

To perform a hot upgrade, one or more ConText servers must be running with the DDL (D) personality. In addition, to perform queries, one or more ConText servers must be running with the Query (Q) personality.

You can start a ConText server by calling the ctxsrv/ctxsrvx executable from the command-line or through the ctxctl utility.

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

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

Note: The syntax for this example is specific to a UNIX-based operating system.

In this example, the password for the CTXSYS user is 'ctxsys.' The server is started in the background and information for the session is written to a file name ctx.log.

An additional personality, DML (M), can be assigned to ConText servers. ConText servers with the DML personality automatically reindex a text table when changes which affect the text index are made to the table.

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

Additional Information:
Oracle ConText Option Administrator's Guide

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 storage method, and the options that ConText Option uses to create an index for the column.

Once a text indexing policy is created for a column and text is stored in the column, the column is ready for text indexing.

To define a text indexing policy for a column, call the CREATE_POLICY procedure in the CTX_DDL PL/SQL package 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 policy name CTX_DOCS is created, using all of the default ConText Option indexing options, for the TEXT column in the DOCS table owned by CTXDEV.

The ConText Option default indexing options are:

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)

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.

Additional Information:
Oracle ConText Option Administrator's Guide

Text Queries

A text query is any query that includes search criteria for text stored in text columns in the queried table(s).

Before you can perform a text query, you must create a text index for the text column you want to query.

In addition, if you want to use two-step queries, you must create a result table which is used to store the results of the query.

Once these tasks have been performed, you can use any one of the three supported query methods to query text in the column: two-step, one-step, or in-memory.

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

Additional Information:
Oracle ConText Option Administrator's Guide

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) for the documents that satisfy the search conditions you specify in the first step of a two-step query.

The table also stores a score for each document. The score, generated by ConText Option, measures the relevance of a document to the query based on the number of occurrences of the query terms in the document.

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 a query.

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

Additional Information:
Oracle7 Server SQL Reference,
Oracle ConText Option Application Developer's Guide

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.

Attention: 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 or retrieve 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:

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

	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 by a query to create a hitlist which lists the SCORE and TITLE of each document in which the terms oracle or lotus occurs.

Additional Information:
Oracle ConText Option Application Developer's Guide

One-Step Query Example

One-step queries use a ConText Option 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 the two-step query example:

	select score(1), pk, title from docs
	where contains(text, 'lotus | oracle', 1) > 0
 	order by score(1) desc;

Additional Information:
Oracle ConText Option Application Developer's Guide

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.

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.

The following example illustrates an in-memory query that returns the same results as the two-step and one-step query examples:

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

Additional Information:
Oracle ConText Option Application Developer's Guide

Prev Next

Library

Product

Contents