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');­