ORACLE TEXT: Queries on Catalog Information

This example creates a catalog index for an auction site that sells electronic equipment such as cameras and CD players. New inventory is added everyday and item descriptions, bid dates, and prices must be stored together. The application requires good response time for mixed queries. The key is to determine what columns users frequently search so that we can create a suitable CTXCAT index. Queries on this type of index are issued with the CATSEARCH operator.

Create Your Table

Set up an auction table to store your inventory:

CREATE TABLE auction(
item_id NUMBER,
title VARCHAR2(100),
category_id NUMBER,
price NUMBER,
bid_close DATE);


Populate Your Table

Now populate the table with various items, each with an id, title, price and bid_date:

INSERT INTO AUCTION VALUES(1, 'NIKON CAMERA', 1, 400, '24-OCT-2002'); INSERT INTO AUCTION VALUES(2, 'OLYMPUS CAMERA', 1, 300, '25-OCT-2002'); INSERT INTO AUCTION VALUES(3, 'PENTAX CAMERA', 1, 200, '26-OCT-2002'); INSERT INTO AUCTION VALUES(4, 'CANON CAMERA', 1, 250, '27-OCT-2002');


Determine your Queries

You need to determine what criteria are likely to be retrieved. In this example, you determine that all queries search the title column for item descriptions, and most queries order by price. When using the CATSEARCH operator later, we'll specify the terms for the text column and the criteria for the structured clause. Step 2 Create the Sub-Index to Order by Price For Oracle Text to serve these queries efficiently, we need a sub-index for the price column, since our queries will order by price. Therefore, create an index set called auction_set and add a sub-index for the price column:

EXEC CTX_DDL.CREATE_INDEXT_SET('auction_iset'); EXEC CTX_DDL.ADD_INDEX('auction_iset','price'); /* sub-index A*/


Create the CTXCAT Index

Create the combined catalog index on the AUCTION table with CREATE INDEX as follows:

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

Querying Your Table with CATSEARCH : When you have created the CTXCAT index on the AUCTION table, you can query this index with the CATSEARCH operator. First set the output format to make the output readable:

COLUMN title FORMAT a40;
SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'order by price')> 0;
SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'price <= 300')>0;

Update Your Table

You can update your catalog table by adding new rows. When you do so, the CTXCAT index is automatically synchronized to reflect the change. For example, add the following new rows to our table and then reexecute the query:

INSERT INTO AUCTION VALUES(5, 'FUJI CAMERA', 1, 350, '28-OCT-2002');
INSERT INTO AUCTION VALUES(6, 'SONY CAMERA', 1, 310, '28-OCT-2002');

SELECT title, price FROM auction WHERE CATSEARCH(title, 'CAMERA', 'order by price')> 0;

Related Topics:

1. Example of Text Queries on Document Collections.
2. Example of Document Classification.