1.=======为拥有文档表的模式建立文本索引授权
====以system用户连接数据库,为ZYFILEUP授权。
GRANT "CTXAPP" TO "JDCDC";
ALTER USER "JDCDC" DEFAULT ROLE ALL;
GRANT EXECUTE ON "CTXSYS"."CTX_ADM" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_CATSEARCH" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_CONTAINS" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_DDL" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_DOC" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_QUERY" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_ULEXER" TO "JDCDC";
GRANT EXECUTE ON "CTXSYS"."CTX_XPCONTAINS" TO "JDCDC";
2.========建立文档表在BLOB_CONTENT列上的索引=========
=====以JDCDC用户连接数据库,建立文本索引参考项
begin
ctx_ddl.create_preference('mylex','BASIC_LEXER');
ctx_ddl.set_attribute('mylex','printjoins','_-');
ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH',1);
ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', 5);
ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
end;
begin
ctx_ddl.create_preference('cnlex','CHINESE_LEXER');
--====ctx_ddl.create_preference('chinalex','chinese_vgram_lexer')===---;
end;
3.==========建立索引=============
create index name_index on cdc.test(name)
indextype is ctxsys.context
parameters ('DATASTORE CTXSYS.DIRECT_DATASTORE
FILTER CTXSYS.INSO_FILTER
LEXER jdcdc.CNLEX
WORDLIST jdcdc.MYWORDLIST');
<<<
create index name_index on cdc.test(name)
indextype is ctxsys.context
parameters ('LEXER cdc.CNLEX');
>>>>
==========删除索引=============
drop index name_index;
4.==========索引同步=============
exec ctx_ddl.sync_index('test_index');
exec ctx_ddl.sync_index('name_index');
exec ctx_ddl.sync_index('subject_index');
exec ctx_ddl.sync_index('contents_index');
5.==========文本查询语句格式=====
select name from cdc.test
where contains(name,'aa',1)>0
order by name;
CREATE TABLE zlk_poisoninf (
datainfoid NUMBER PRIMARY KEY,
summarize clob,
DIAGNOSE clob,
TREATMENT clob
);
create index summarize_fullindex on cdc.zlk_poisoninf(summarize)
indextype is ctxsys.context
parameters ('LEXER cdc.CNLEX');
exec ctx_ddl.sync_index('summarize_fullindex');