Oracle Text Reference Release 9.0.1 Part Number A90121-01 |
|
CTX_DDL Package, 4 of 28
Use this procedure to add an index to a catalog index preference. You create this preference to create catalog indexes of type CTXCAT.
CTX_DDL.ADD_INDEX(set_name in varchar2,column_list varchar2, storage_clause varchar2);
Specify the name of the index set.
Specify a comma separated list of columns to index.
Specify a storage clause.
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');
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;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|