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
  • Start ConText Servers
  • Perform Hot Upgrade of Columns
• Text Queries
  • Create Text Indexes for Text Columns
  • Create Result 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 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 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:

$ ctxsrv -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, 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.

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.

See Also: Oracle8 ConText Cartridge 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 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 parameters:

• policy name
• fully qualified column name for the policy
• indexing options

Each indexing option, also known as a preference, answers one of the questions that ConText requires to create an index:

Indexing Option	Question Answered	Default Values
Data Store	How is the text stored in the column?	Text stored directly in the column, each row in the table represents a separate, complete document
Filter	What format is the text in?	Plain (ASCII) text
Lexer	What language is the text in and how are tokens identified in the text?	English or other Western European languages; tokens identified by blank space(s); standard punctuation and non-alphanumeric characters; case-sensitivity disabled
Engine	How is the ConText index created and where is it stored	ConText index created using approximately 12.5 Mb of memory; index stored in user's tablespace
Stoplist	Which tokens are not included as entries in the index?	Supplied list of English stopwords
Wordlist	Which special query options are enabled for the index	Soundex query expansion disabled; fuzzy matching and stemming set to English

The following example creates a text indexing policy named ctx_docs for the text column in the docs table owned by ctxdev. Because this example uses all the default ConText indexing options, no preferences have to be explicitly set for ctx_docs:

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

If you want to specify different indexing options for a policy, you can specify corresponding preferences when you call CREATE_POLICY.

For example, if you want to enable case-sensitive lexing, which, in turn, enables case-sensitive text queries, you can create a Lexer preference with case-sensitive lexing enabled, then use the preference when creating a policy with CREATE_POLICY.

See Also: Oracle8 ConText Cartridge Administrator's Guide

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:

• Create Text Indexes for Text Columns
• Create Result Tables (Two-Step Queries Only)

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

For examples of the query methods, see "Two-Step Query Example", "One-Step Query Example", or "In-Memory Query Example" in this chapter.

Note: All of the text query examples are case-insensitive because case-sensitive lexing is disabled for the ctx_docs policy.

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.

See Also: Oracle8 ConText Cartridge 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) 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 varchar2(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 from either a single user or multipe users.

See Also: Oracle8 Server SQL Reference, Oracle8 ConText Cartridge 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.

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:

1. begin

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

1. 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 returned in step one.

See Also: Oracle8 ConText Cartridge Application Developer's Guide

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;

See Also: Oracle8 ConText Cartridge 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

.

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.

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.

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 the score and pk columns in the query results.

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

See Also: Oracle8 ConText Cartridge Application Developer's Guide

---