SELECT ID,name FROM table WHERE dbms_lob.instr(table.clob,'Keys',1,1) > 0; z这个是clob的全文检索,但是对中文却不行 借楼主的帖看看有没有完美方案
以下是我工作中用到的一个SQL脚本,不太完善,但是能说明一定的问题,不要问我其中每个语句是什么意思,在很多的文档都是来说明它的,我怎么说也不会说清楚的,呵,祝好运begin ctx_ddl.DROP_PREFERENCE('file_path'); end; / begin ctx_ddl.create_preference('file_path','FILE_DATASTORE'); ctx_ddl.set_attribute('file_path','PATH','e:\yu1\adobe\word'); end; / begin ctxsys.ctx_ddl.DROP_PREFERENCE('sub_string'); end; /begin ctx_ddl.create_preference('sub_string', 'BASIC_WORDLIST'); ctx_ddl.set_attribute('sub_string','SUBSTRING_INDEX','TRUE'); ctx_ddl.set_attribute('sub_string','FUZZY_MATCH', 'CHINESE_VGRAM');end; /begin Ctx_Ddl.Drop_Preference ( 'mystore'); end; / begin Ctx_Ddl.Create_Preference('mystore', 'BASIC_STORAGE'); ctx_ddl.set_attribute('mystore','I_TABLE_CLAUSE', 'tablespace bv55_index'); ctx_ddl.set_attribute('mystore','K_TABLE_CLAUSE', 'tablespace bv55_index '); ctx_ddl.set_attribute('mystore','R_TABLE_CLAUSE', 'tablespace bv55_index '); ctx_ddl.set_attribute('mystore','N_TABLE_CLAUSE', 'tablespace bv55_index '); ctx_ddl.set_attribute('mystore','I_TABLE_CLAUSE', 'tablespace bv55_index '); end; / drop index idx_bv_edit_file1 force; create index idx_bv_edit_file1 on bv_editorial1(content_file) indextype is ctxsys.context parameters ('datastore file_path filter ctxsys.inso_filter wordlist sub_string storage mystore');drop index idx_bv_edit0_long1 force; create index idx_bv_edit0_long1 on bv_editorial1(long_txt) indextype is ctxsys.context parameters ('wordlist sub_string storage mystore');begin ctxsys.ctx_ddl.DROP_PREFERENCE('sub_string'); end; /begin ctx_ddl.create_preference('sub_string', 'BASIC_WORDLIST'); ctx_ddl.set_attribute('sub_string','SUBSTRING_INDEX','TRUE'); ctx_ddl.set_attribute('sub_string','FUZZY_MATCH', 'CHINESE_VGRAM');end; / begin ctx_ddl.CREATE_PREFERENCE('my_lexer','chinese_vgram_lexer'); end; /
begin ctx_ddl.DROP_PREFERENCE('file_path'); end; / begin ctx_ddl.create_preference('file_path','FILE_DATASTORE'); end; /
drop index idx_test_text_filename force; create index idx_test_text_filename on test_text(filename) indextype is ctxsys.context parameters('datastore file_path filter ctxsys.inso_filter lexer my_lexer wordlist sub_string stoplist ctxsys.empty_stoplist');
drop index idx_law_info_content force; create index idx_law_info_content on law_info (content) indextype is ctxsys.context parameters (' lexer my_lexer wordlist sub_string stoplist ctxsys.empty_stoplist');
create index idx_law_info_content on law_info (content) indextype is ctxsys.context
drop index idx_info_content force; create index idx_info_content on info_content (content) indextype is ctxsys.context parameters ('lexer my_lexer wordlist sub_string stoplist ctxsys.empty_stoplist');
--drop index idx_test_content; --create index idx_test_content on test (content) indextype is ctxsys.context parameters ('lexer my_lexer wordlist sub_string stoplist ctxsys.empty_stoplist');上面要注意的就是建索引的方式与常规索引不同
WHERE dbms_lob.instr(table.clob,'Keys',1,1) > 0; z这个是clob的全文检索,但是对中文却不行 借楼主的帖看看有没有完美方案
ctx_ddl.DROP_PREFERENCE('file_path');
end;
/
begin
ctx_ddl.create_preference('file_path','FILE_DATASTORE');
ctx_ddl.set_attribute('file_path','PATH','e:\yu1\adobe\word');
end;
/
begin
ctxsys.ctx_ddl.DROP_PREFERENCE('sub_string');
end;
/begin
ctx_ddl.create_preference('sub_string', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('sub_string','SUBSTRING_INDEX','TRUE');
ctx_ddl.set_attribute('sub_string','FUZZY_MATCH', 'CHINESE_VGRAM');end;
/begin
Ctx_Ddl.Drop_Preference ( 'mystore');
end;
/ begin
Ctx_Ddl.Create_Preference('mystore', 'BASIC_STORAGE');
ctx_ddl.set_attribute('mystore','I_TABLE_CLAUSE',
'tablespace bv55_index');
ctx_ddl.set_attribute('mystore','K_TABLE_CLAUSE',
'tablespace bv55_index ');
ctx_ddl.set_attribute('mystore','R_TABLE_CLAUSE',
'tablespace bv55_index ');
ctx_ddl.set_attribute('mystore','N_TABLE_CLAUSE',
'tablespace bv55_index ');
ctx_ddl.set_attribute('mystore','I_TABLE_CLAUSE',
'tablespace bv55_index ');
end;
/ drop index idx_bv_edit_file1 force;
create index idx_bv_edit_file1 on bv_editorial1(content_file)
indextype is ctxsys.context
parameters ('datastore file_path filter ctxsys.inso_filter wordlist sub_string storage mystore');drop index idx_bv_edit0_long1 force;
create index idx_bv_edit0_long1 on bv_editorial1(long_txt)
indextype is ctxsys.context
parameters ('wordlist sub_string storage mystore');begin
ctxsys.ctx_ddl.DROP_PREFERENCE('sub_string');
end;
/begin
ctx_ddl.create_preference('sub_string', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('sub_string','SUBSTRING_INDEX','TRUE');
ctx_ddl.set_attribute('sub_string','FUZZY_MATCH', 'CHINESE_VGRAM');end;
/
begin
ctx_ddl.CREATE_PREFERENCE('my_lexer','chinese_vgram_lexer');
end;
/
begin
ctx_ddl.DROP_PREFERENCE('file_path');
end;
/
begin
ctx_ddl.create_preference('file_path','FILE_DATASTORE');
end;
/
drop index idx_test_text_filename force;
create index idx_test_text_filename on test_text(filename)
indextype is ctxsys.context
parameters('datastore file_path filter ctxsys.inso_filter lexer my_lexer wordlist sub_string stoplist ctxsys.empty_stoplist');
drop index idx_law_info_content force;
create index idx_law_info_content
on law_info (content)
indextype is ctxsys.context
parameters (' lexer my_lexer wordlist sub_string stoplist ctxsys.empty_stoplist');
create index idx_law_info_content
on law_info (content)
indextype is ctxsys.context
drop index idx_info_content force;
create index idx_info_content on info_content (content) indextype is ctxsys.context parameters ('lexer my_lexer wordlist sub_string stoplist ctxsys.empty_stoplist');
--drop index idx_test_content;
--create index idx_test_content on test (content) indextype is ctxsys.context parameters ('lexer my_lexer wordlist sub_string stoplist ctxsys.empty_stoplist');上面要注意的就是建索引的方式与常规索引不同