今天测试了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>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/81227/viewspace-692471/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/81227/viewspace-692471/