Oracle Text - Part 3

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

By including structured information such as price and date within the CTXCAT index, we are able to make this search very much more efficient.When is a CTXCAT index NOT suitable?

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

There are also differences in the time and space needed to create the index. CTXCAT indexes take quite a bit longer to create - and use considerably more disk space - than CONTEXT indexes. If you are tight on disk space, you should consider carefully whether CTXCAT indexes are appropriate for you.Any other differences?

Yes - in DML processing (updates, inserts and deletes).CTXCAT indexes are transactional.Where a CONTEXT index uses a "deferred indexing" method - CTXCAT indexes work much more like a normal (b-tree) index. When you commit changes, all necesary changes to the CTXCAT indexes take place before the commit returns

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

EG:
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. if 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.CTX_DDL package allow 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');

how to search ?

By using the CATSEARCH operator instead of the CONTAINS operator used for
a CONTEXT index.If 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 no structured clause is provided "null" Must be used here.

The structured clause allows to restrict, or sort,the results.the query can be extended
above to find only items costing less than $100,

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

and want to find the results with the newest items first

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

--------------------------------------------
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 - by doing this index-creation times reduce by a factor of 10 . 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.