--1. Create Table
CREATE TABLE products (
    id           NUMBER,
    product_name VARCHAR2(200),
    category     VARCHAR2(100),
    price        NUMBER
);
--2. Sample data
INSERT INTO products VALUES (1, 'Gaming Laptop',  'Electronics', 1500);
INSERT INTO products VALUES (2, 'Office Laptop',  'Electronics',  900);
INSERT INTO products VALUES (3, 'Wireless Mouse', 'Accessories',   50);
COMMIT;
--3. Create CTXCAT index set
BEGIN
  CTX_DDL.CREATE_INDEX_SET('prod_iset');
END;
/
--4. Add structured columns
BEGIN
  CTX_DDL.ADD_INDEX('prod_iset', 'price');
  CTX_DDL.ADD_INDEX('prod_iset', 'category');
END;
/
--5. Create CTXCAT index using that index set
CREATE INDEX idx_products_ctxcat
ON products(product_name)
INDEXTYPE IS CTXSYS.CTXCAT
PARAMETERS ('index set prod_iset');
--6. Query
SELECT id, product_name, category, price
FROM products
WHERE CATSEARCH(
        product_name,
        'laptop',
        'price < 1000 AND category = ''Electronics'''
      ) > 0;
      
      This works with a plain CTXCAT index:
--If you do not want PRICE inside the CTXCAT structured clause, then do not write it there.
SELECT *
FROM products
WHERE CATSEARCH(product_name, 'laptop', NULL) > 0;
