Creating a CTXCAT Index

4.6 Creating a CTXCAT Index

The CTXCAT index type is well-suited for indexing small text fragments and related information.

This index type provides better structured query performance than a CONTEXT index.

Note:

The Oracle Text indextype CTXCAT is deprecated with Oracle Database 23ai. The indextype itself, and it's operator CTXCAT, can be removed in a future release.

Both CTXCAT and the use of CTXCAT grammar as an alternative grammar for CONTEXT queries is deprecated. Instead, Oracle recommends that you use the CONTEXT indextype, which can provide all the same functionality, except that it is not transactional. Near-transactional behavior in CONTEXT can be achieved by using SYNC(ON COMMIT) or, preferably, SYNC(EVERY [time-period]) with a short time period.

CTXCAT was introduced when indexes were typically a few megabytes in size. Modern, large indexes, can be difficult to manage with CTXCAT. The addition of index sets to CTXCAT can be achieved more effectively by the use of FILTER BY and ORDER BY columns, or SDATA, or both, in the CONTEXT indextype. CTXCAT is therefore rarely an appropriate choice. Oracle recommends that you choose the more efficient CONTEXT indextype.

4.6.1 CTXCAT Index and DML Operations

A CTXCAT index is transactional. When you perform inserts, updates, and deletes on the base table, Oracle Text automatically synchronizes the index. Unlike a CONTEXT index, no CTX_DDL.SYNC_INDEX is necessary.

Note:

Applications that insert without invoking triggers, such as SQL*Loader, do not result in automatic index synchronization as described in this section.

4.6.2 About CTXCAT Subindexes and Their Costs

A CTXCAT index contains subindexes that you define as part of your index set. You create a subindex on one or more columns to improve mixed query performance. However, the time Oracle Text takes to create a CTXCAT index depends on its total size, and the total size of a CTXCAT index is directly related to the following factors:

Total text to be indexed
Number of subindexes in the index set
Number of columns in the base table that make up the subindexes

Many component indexes in your index set also degrade the performance of insert, update, and delete operations, because more indexes must be updated.

Because of the added index time and disk space costs for creating a CTXCAT index, before adding it to your index set, carefully consider the query performance benefit that each component index gives your application.

Note:

You can use I_ROWID_INDEX_CLAUSE of BASIC_STORAGE to speed up creation of a CTXCAT index. This clause is described in Oracle Text Reference.

4.6.3 Creating CTXCAT Subindexes

An online auction site that must store item descriptions, prices, and bid-close dates for ordered look-up is a good example for creating a CTXCAT index.

Figure 4-1 Auction Table Schema and CTXCAT Index

Description of "Figure 4-1 Auction Table Schema and CTXCAT Index"

Figure 4-1 shows a table called AUCTION with the following schema:

create table auction(
item_id number,
title varchar2(100),
category_id number,
price number,
bid_close date);

To create your subindexes, create an index set to contain them:

begin
ctx_ddl.create_index_set('auction_iset');
end;

Next, determine the structured queries that you are likely to enter. The CATSEARCH query operator takes a mandatory text clause and optional structured clause.

In the example, this means that all queries include a clause for the title column, which is the text column.

Assume that the structured clauses fall into the following categories:

Structured Clauses	Subindex Definition to Serve Query	Category
'price < 200' 'price = 150' 'order by price'	'price'	A
'price = 100 order by bid_close' 'order by price, bid_close'	'price, bid_close'	B

Structured Clauses

Subindex Definition to Serve Query

Category

'price < 200'

'price = 150'

'order by price'

'price'

'price = 100 order by bid_close'

'order by price, bid_close'

'price, bid_close'

Structured Query Clause Category A

The structured query clause contains an expression only for the price column as follows:

SELECT FROM auction WHERE CATSEARCH(title, 'camera', 'price < 200')> 0;
SELECT FROM auction WHERE CATSEARCH(title, 'camera', 'price = 150')> 0;
SELECT FROM auction WHERE CATSEARCH(title, 'camera', 'order by price')> 0;

These queries can be served by using subindex B. However, for efficiency, you can also create a subindex only on price (subindex A):

begin
ctx_ddl.add_index('auction_iset','price'); /* sub-index A */
end;

Structured Query Clause Category B

The structured query clause includes an equivalent expression for price ordered by bid_close, and an expression for ordering by price and bid_close, in that order:

SELECT FROM auction WHERE CATSEARCH(
   title, 'camera','price = 100 
   ORDER BY bid_close')> 0;
SELECT FROM auction 
   WHERE CATSEARCH(
   title, 'camera','order by price, bid_close')> 0;

These queries can be served with a subindex defined as follows:

begin
ctx_ddl.add_index('auction_iset','price, bid_close'); /* sub-index B */
end;

Like a combined b-tree index, the column order that you specify with CTX_DDL.ADD_INDEX affects the efficiency and viability of the index scan which Oracle Text uses to serve specific queries. For example, if two structured columns p and q have a b-tree index specified as 'p,q', Oracle Text cannot scan this index to sort 'ORDER BY q,p'.

4.6.4 Creating CTXCAT Index

This example combines the previous examples and creates the index set preference with the two subindexes:

begin
ctx_ddl.create_index_set('auction_iset');
ctx_ddl.add_index('auction_iset','price'); /* sub-index A */
ctx_ddl.add_index('auction_iset','price, bid_close'); /* sub-index B */
end;

Figure 4-1 shows how the subindexes A and B are created from the auction table. Each subindex is a b-tree index on the text column and the named structured columns. For example, subindex A is an index on the title column and the bid_close column.

You create the combined catalog index with the CREATE INDEX statement as follows:

CREATE INDEX auction_titlex ON AUCTION(title) 
  INDEXTYPE IS CTXSYS.CTXCAT 
  PARAMETERS ('index set auction_iset')
;