ADD_INDEX

Use this procedure to add an index to a catalog index preference. You create this preference to create catalog indexes of type CTXCAT.

Syntax

CTX_DDL.ADD_INDEX(set_name in varchar2,

column_list varchar2,
storage_clause varchar2);

set_name

Specify the name of the index set.

column_list

Specify a comma separated list of columns to index.

storage_clause

Specify a storage clause.

Example

Consider 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);

Assume that queries on the table involve a mandatory text query clause and optional structured conditions on category_id. Results must be sorted based on bid_close.

You can create a catalog index to support the different types of structured queries a user might enter.

To create the indexes, first create the index set preference then add the required indexes to it:

begin
ctx_ddl.create_index_set('auction_iset');
ctx_ddl.add_index('auction_iset','bid_close');
ctx_ddl.add_index('auction_iset','category_id, bid_close');
ctx_ddl.add_index('auction_iset','price, bid_close');
end;

Create the combined catalog index with CREATE INDEX as follows:

create index auction_titlex on AUCTION(title) indextype is CTXCAT parameters 
('index set auction_iset');

Querying

To query the title column for the word pokemon, you can issue regular and mixed queries as follows:

select * from AUCTION where CATSEARCH(title, 'pokemon',NULL)> 0;
select * from AUCTION where CATSEARCH(title, 'pokemon', 'category_id=99 order by 
bid_close desc')> 0;