Oracle全文檢索之Ctxcat 索引

今天測試了Ctxcat 索引，發現也不支援中文分詞。

 

SQL> Create table auction(Item_id number,Title varchar2(100),Category_id number,Price number,Bid_close date);

Table created

SQL> Insert into auction values(1, 'nikon camera', 1, 400, to_date('24-10-2002','dd-mm-yyyy'));

1 row inserted

SQL> Insert into auction values(2, 'olympus camera', 1, 300, to_date('25-10-2002','dd-mm-yyyy'));

1 row inserted

SQL> Insert into auction values(3, 'pentax camera', 1, 200, to_date('26-10-2002','dd-mm-yyyy'));

1 row inserted

SQL> Insert into auction values(4, 'canon camera', 1, 250, to_date('27-10-2002','dd-mm-yyyy'));

1 row inserted

SQL> Commit;

Commit complete

--建立索引集
SQL> begin
  2  ctx_ddl.create_index_set('auction_iset');
  3  ctx_ddl.add_index('auction_iset','price'); /* sub-index a*/
  4  end;
  5  /

PL/SQL procedure successfully completed

--建立索引
SQL> Create index auction_titlex on auction(title) indextype is ctxsys.ctxcat parameters ('index set auction_iset');

Index created

SQL> Column title format a60;
SQL> Select title, price from auction where catsearch(title, 'camera', 'order by price')> 0;

TITLE                                                             PRICE
------------------------------------------------------------ ----------
pentax camera                                                       200
canon camera                                                        250
olympus camera                                                      300
nikon camera                                                        400

--測試索引是否自動同步
SQL> Insert into auction values(5, 'aigo camera', 1, 10, to_date('27-10-2002','dd-mm-yyyy'));

1 row inserted

SQL> Insert into auction values(6, 'len camera', 1, 23, to_date('27-10-2002','dd-mm-yyyy'));

1 row inserted

SQL> commit;

Commit complete

SQL> Select title, price from auction where catsearch(title, 'camera','price <= 100')>0;

TITLE                                                             PRICE
------------------------------------------------------------ ----------
aigo camera                                                          10
len camera                                                           23

SQL> --新增多個子查詢到索引集：
SQL> begin
  2  ctx_ddl.drop_index_set('auction_iset');
  3  ctx_ddl.create_index_set('auction_iset');
  4  ctx_ddl.add_index('auction_iset','price'); /* sub-index A */
  5  ctx_ddl.add_index('auction_iset','price, bid_close'); /* sub-index B */
  6  end;
  7  /

PL/SQL procedure successfully completed

SQL> drop index auction_titlex;

Index dropped

SQL> Create index auction_titlex on auction(title) indextype is ctxsys.ctxcat parameters ('index set auction_iset');

Index created

SQL> SELECT * FROM auction WHERE CATSEARCH(title, 'camera','price = 200 order by bid_close')>0;

   ITEM_ID TITLE                                                        CATEGORY_ID      PRICE BID_CLOSE
---------- ------------------------------------------------------------ ----------- ---------- -----------
         3 pentax camera                                                          1        200 2002/10/26

SQL> SELECT * FROM auction WHERE CATSEARCH(title, 'camera','order by price, bid_close')> 0;

   ITEM_ID TITLE                                                        CATEGORY_ID      PRICE BID_CLOSE
---------- ------------------------------------------------------------ ----------- ---------- -----------
         5 aigo camera                                                            1         10 2002/10/27
         6 len camera                                                             1         23 2002/10/27
         3 pentax camera                                                          1        200 2002/10/26
         4 canon camera                                                           1        250 2002/10/27
         2 olympus camera                                                         1        300 2002/10/25
         1 nikon camera                                                           1        400 2002/10/24

6 rows selected

SQL>
SQL> --測試中文支援
SQL> Insert into auction values(15, '佳能照相機', 1, 3700, to_date('27-10-2002','dd-mm-yyyy'));

1 row inserted

SQL> Insert into auction values(16, '海爾洗衣機', 1, 2300, to_date('27-10-2002','dd-mm-yyyy'));

1 row inserted

SQL> commit;

Commit complete

SQL> SELECT * FROM auction WHERE CATSEARCH(title, '照相機','order by price, bid_close')> 0;

   ITEM_ID TITLE                                                        CATEGORY_ID      PRICE BID_CLOSE
---------- ------------------------------------------------------------ ----------- ---------- -----------

SQL> Select title, price from auction ;

TITLE                                                             PRICE
------------------------------------------------------------ ----------
nikon camera                                                        400
olympus camera                                                      300
pentax camera                                                       200
canon camera                                                        250
aigo camera                                                          10
len camera                                                           23
佳能照相機                                                         3700
海爾洗衣機                                                         2300

8 rows selected

SQL>