Oracle full-Text retrieval notes

1. Delete the lexical parser

exec ctx_ddl.drop_preference (' my_lexer ');

2. Create Chinese lexical parser

exec ctx_ddl.create_preference (' my_lexer ', ' chinese_vgram_lexer ');

3. Create full-text index, multi-field

EXEC ctx_ddl.create_preference (' ctx_idx_jdw_person_pref ', ' multi_column_datastore ');

EXEC ctx_ddl.set_attribute (' ctx_idx_jdw_person_pref ', ' columns ', ' name,address ');

CREATE INDEX Ctx_idx_jdw_person on Jdw_person (name) Indextype is Ctxsys. CONTEXT PARAMETERS (' DATASTORE ctx_idx_jdw_person_pref lexer my_lexer ');

4. Cut words

EXEC ctx_ddl. Create_policy (' my_policy ', LEXER = ' my_lexer ');

Create or Replace function P_split_chinese (p_input in VARCHAR2)
return VARCHAR2 AS
V_tab Ctx_doc. Token_tab;
V_return VARCHAR2 (323767);
Begin
Ctx_doc. Policy_tokens (' My_policy ', p_input,v_tab);
For I in 1..v_tab.count loop
V_return: = V_return | | ', ' | | V_tab (i). token;
End Loop;
Return LTRIM (V_return, ', ');
End
/

5. Synchronization and optimization

exec ctx_ddl.sync_index (' Ctx_idx_jdw_person ');

exec ctx_ddl.optimize_index (' Ctx_idx_jdw_person ', ' full ');

6. Create a timed task to periodically optimize and synchronize the domain index

sql> Create or replace procedure Hsp_sync_index as
2 begin
3 Ctx_ddl.sync_index (' id_cont_msg ');
4 End;
5/

Procedure created.

elapsed:00:00:00.08
Sql> VARIABLE jobno number;
Sql> BEGIN
2 dbms_job. SUBMIT (: Jobno, ' hsp_sync_index (); ',
3 Sysdate, ' Sysdate + (1/24/4) ');
4 commit;
5 END;
6/

PL/SQL procedure successfully completed.

elapsed:00:00:00.27
sql> Create or replace procedure Hsp_optimize_index as
2 begin
3 Ctx_ddl.optimize_index (' id_cont_msg ', ' full ');
4 End;
5/

Sql> VARIABLE jobno number;
Sql> BEGIN
2 dbms_job. SUBMIT (: Jobno, ' hsp_optimize_index (); ',
3 Sysdate, ' sysdate + 1 ');
4 commit;
5 END;
6/
Procedure created.

elapsed:00:00:00.03

PL/SQL procedure successfully completed.

elapsed:00:00:00.02
Sql>

Oracle full-Text retrieval notes