The CTXCAT Index Type - what is it, why should I use it?

Introduction

Oracle 8i introduced the CONTEXT index type - a "domain" index that was used for fast retrieval of unstructured text.

CTXCAT indexes work best when text is in "small chunks" - maybe a couple of lines maximum - and searches need to restrict and/or sort the result set according to certain structured criteria - usually numbers or dates.

For example, lets consider an on-line auction site. Each item for sale has a short description, a current bid price and dates for the start and end of the auction. A user might want to see all the records with "antique cabinet" in the description, with a current bid price less than $500. Since he's particularly interested in new items, he wants the results sorted by auction start time.

Such a search would be fairly inefficient using a normal CONTEXT index. The kernel would have to find all the records that matched the text search, then restrict the set to those with the correct price (which requires the use of a different index), and then sort the results using a third index.

The query language with CTXCAT is considerably simpler than for CONTEXT indexes. Basically, you can search for phrases and words (with wild cards if required), using AND and OR operators, but that's about it. If your application needs more complex text retrieval featurs - such as stemming, thesaurus, fuzzy matching and so on, you should be using a CONTEXT index.

  CREATE INDEX indexname ON table(column) 
  INDEXTYPE IS CTXCAT;

However, if you do this you're not taking advantage of the ability to include structured information within the index itself.

Let's consider our online auction site mentioned previously:

  create table auction (

    item_id           NUMBER   PRIMARY KEY,  -- auction item identifier
    item_desc         VARCHAR2(80),          -- free-form item description
    price             NUMBER,                -- current price of the item
    start_time        DATE                   -- end time of the auction
    end_time          DATE                   -- end time of the auction
  )

A CTXCAT index (like a CONTEXT index) is a "domain" index. Therefore it supports the "PARAMETERS" clause. A number of possible parameter settings are shared with CONTEXT indexes. These are: LEXER, MEMORY, STOPLIST, STORAGE and WORDLIST (no other CONTEXT parameters are supported). Howevwer, the most important parameter is a new one: INDEX SET.

INDEX SET defines the structured columns that are to be included in the CTXCAT index. Let's say I want to create an index on the item_desc column, but I need to be able to limit my search results by price, and sort by start_time. I do this by creating a new INDEX SET, and adding the structured columns to it. New (for 8.1.7) procedures in the CTX_DDL package allow me to do this:

  ctx_ddl.create_index_set('auction_set');
  ctx_ddl.add_index       ('auction_set', 'price');
  ctx_ddl.add_index       ('auction_set', 'start_time');

Note that the item_desc column is NOT part of the INDEX SET. item_desc is only mentioned when we come to create the actual index:

  CREATE INDEX auction_index ON auction (item_desc) 
  INDEXTYPE IS CTXCAT
  PARAMETERS ('INDEX SET auction_set');

And how do I do a search?

First of all, we'll be using the CATSEARCH operator instead of the CONTAINS operator used for a CONTEXT index. Let's ignore the structured columns for now and look at the basic query syntax. I want to find all auction items which contains the words "toy" and "dog" but not the phrase "live animal":

 SELECT item_id, item_desc
 FROM auction

 WHERE CATSEARCH (item_desc, '(toy dog) | "live animal"', null) > 0;

A few points to note:

• ANDed terms do not need the word AND or even an "&" operator. AND is assumed between any adjacent terms.
• NOT is represented by "|" (OR, not used here, is represented by "|")
• Parentheses can be used to group terms
• Double quotes are used to surround a phrase (otherwise "live animal" would have been read as "live AND animal".

The "null" in the query above is a placeholder for a structured clause. There is no default - if you don't provide any structured clause you MUST put "null" in here.

The structured clause allows you to restrict, or sort, your results. If I wanted to extend the query above to find only items costing less than $100, I could use

  WHERE CATSEARCH (item_desc, '(toy dog) | "live animal"', 'price < 100') > 0

  WHERE CATSEARCH (item_desc, '(toy dog) | "live animal"', 
                   'price < 100 order by start_time desc') > 0

Note that there are restrictions on what can be used in the structured clause. Of course any columns that are mentioned MUST be included in the index set. Additionally, you cannot use functions, expressions, sub-selects and so on. For full details of the restrictions, see the 8.1.7 documentation addendum.

Some combinations of structured criteria and order by might not be able to be processed efficiently by the catsearch index. When this happens, the query will fail with the error:

  DRG-10844: index cannot execute this structured predicate efficiently

The amount of space used by a CTXCAT index will vary according to how many structured columns are included in the index set. Without getting too deep into the technical detail, each structured column requires both more space in the main index table, AND an extra b-tree index on the index table itself.

However, the figures tend to be large.

It is worth noting that the creation of the B-Tree indexes will be considerably quicker if the SORT_AREA_SIZE kernel parameter is increased. The default is 64K - a very low figure. Increasing this to 1MB will have a very significant effect - I've seen index-creation times reduce by a factor of 10 by doing this. However note the specified amount of memory will be used by EVERY process connecting, so great care should be taken with this parameter if the database is shared with many other users. It might be possible to increase it just for index creation, then reduce it later.