Create Oracle Text Index [CTXCAT Index]

Creating a CTXCAT Index
The CTXCAT indextype is well-suited for indexing small text fragments and related information. A CTXCAT index is comprised of sub-indexes that you define as part of your index set. However, adding sub-indexes to the index set has its cost.

Creating CTXCAT Sub-indexes,
create table auction(item_id number,title varchar2(100),category_id number,price number,bid_close date);
To create your sub-indexes, create an index set to contain them:
begin
ctx_ddl.create_index_set('auction_iset');
end;


Now, The CATSEARCH query operator takes a mandatory text clause and optional structured clause.
SELECT FROM auction WHERE CATSEARCH(title, 'camera', 'price < 200')> 0;
begin
ctx_ddl.add_index('auction_iset','price'); /* sub-index A */
end;

SELECT FROM auction WHERE CATSEARCH(title, 'camera','price = 100 order by bid_close')> 0;
begin
ctx_ddl.add_index('auction_iset','price, bid_close'); /* sub-index B */
end;

So, the full procedure is,
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;

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