ORACLE10g 全文索引
创建全文索引,语句如下:
说明:存储表空间、词法分析器一次设置后,今后在此基础上可直接创建全文索引
--设置存储表空间
EXEC ctx_ddl.drop_preference('CTX_STORAGE');
EXEC CTX_DDL.CREATE_PREFERENCE('CTX_STORAGE', 'BASIC_STORAGE');
EXEC CTX_DDL.SET_ATTRIBUTE('CTX_STORAGE', 'I_TABLE_CLAUSE', 'TABLESPACE FEFULLTXT');
EXEC CTX_DDL.SET_ATTRIBUTE('CTX_STORAGE', 'K_TABLE_CLAUSE', 'TABLESPACE FEFULLTXT');
EXEC CTX_DDL.SET_ATTRIBUTE('CTX_STORAGE', 'R_TABLE_CLAUSE', 'TABLESPACE FEFULLTXT');
EXEC CTX_DDL.SET_ATTRIBUTE('CTX_STORAGE', 'N_TABLE_CLAUSE', 'TABLESPACE FEFULLTXT');
EXEC CTX_DDL.SET_ATTRIBUTE('CTX_STORAGE', 'P_TABLE_CLAUSE', 'TABLESPACE FEFULLTXT');
EXEC CTX_DDL.SET_ATTRIBUTE('CTX_STORAGE', 'I_INDEX_CLAUSE', 'TABLESPACE FEFULLTXT');
--设置词法分析器
EXEC ctx_ddl.drop_preference('chs_lexer');
EXEC ctx_ddl.create_preference ('chs_lexer', 'chinese_lexer');
--创建多列全文索引
举例:全文索引创建
EXEC ctx_ddl.drop_preference('ctx_idx_address_pref');
EXEC ctx_ddl.create_preference('ctx_idx_address_pref', 'MULTI_COLUMN_DATASTORE');
EXEC ctx_ddl.set_attribute('ctx_idx_address_pref','columns','name,address,telephone');
DROP INDEX ctx_idx_address;
CREATE INDEX ctx_idx_address ON t_address_info(address)
INDEXTYPE IS ctxsys.CONTEXT PARAMETERS('DATASTORE ctx_idx_address_pref lexer chs_lexer STORAGE CTX_STORAGE');
--创建单列全文索引
举例:创建全文索引
DROP INDEX ctx_idx_temp_receipts;
CREATE INDEX ctx_idx_temp_receipts ON tf_temp_receipts(third_company)
INDEXTYPE IS ctxsys.CONTEXT PARAMETERS ('lexer chs_lexer STORAGE CTX_STORAGE');
DROP INDEX ctx_idx_payments_receipts;
CREATE INDEX ctx_idx_payments_receipts ON tf_payments_receipts(third_company)
INDEXTYPE IS ctxsys.CONTEXT PARAMETERS ('lexer chs_lexer STORAGE CTX_STORAGE');
--查询范例
SELECT * FROM t_address_info WHERE contains (address, '闵行区 or 137888888') > 0;
--同步范例
ctx_ddl.sync_index('ctx_idx_address');
ctx_ddl.optimize_index('ctx_idx_address ','FULL');