Oracle Full-Text Search

Reprinted from: http://blog.sina.com.cn/s/blog_613fee860100yhyz.html

1. The Ctxsys user needs to be unlocked in order to obtain the operation rights of the CTX_DDL package.
Enter the system user, enter the following command to unlock the Ctxsys user
Alter user Ctxsys account unlock;
Then assign the operation permissions of the CTX_DDL package to the CTFs user.
Also under System users, enter the following command to give the target user CTX_DDL package operation permissions
Grant execute on CTX_DDL to CTFs;
At this point, the preparation has been completed.

2. Creating an analyzer
Here, using the Chinese_vgram_lexer, log in with the CTFs user, and execute the following command to create the parser.
exec ctx_ddl.create_preference (' ctfs_lexer ', ' chinese_vgram_lexer ');
This sentence means creating a "chinese_vgram_lexer" parser with the name Ctfs_lexer.

3. Create a filter phrase
Log in with CTFs user, execute the following command to create a filter phrase
exec ctx_ddl.create_stoplist (' ctfs_stoplist ');
To create a filter phrase successfully, you need to customize the phrases you want to filter
exec ctx_ddl.add_stopword (' ctfs_stoplist ', ' limited ');

4. Create a table index
Create INDEX Ctfs_buy_service_index on Ctfs_buy_service (keyword_) indextype is ctxsys.context parameters (' Lexer ctfs_ Lexer stoplist ctfs_stoplist ');
Create INDEX Ctfs_supply_service_index on Ctfs_supply_service (keyword_) indextype is ctxsys.context parameters (' Lexer Ctfs_lexer stoplist ctfs_stoplist ');

5. Using the index
Select Score (1), b.* from Ctfs_buy_service where contains (Keyword_, ' Java development ', 1) >0 ORDER by score (1) desc;
The score here is the score computed for the degree to which the Oracle full-text search matches the keyword, and the last parameter in contains, "1", is an identification of the score.

6. Index optimization (for data changes: Add, delete, modify)
Index synchronization:
exec ctx_ddl.sync_index (' Ctfs_buy_service_index ');
exec ctx_ddl.sync_index (' Ctfs_supply_service_index ');
Index optimization:
exec ctx_ddl.optimize_index (' Ctfs_buy_service_index ', full);
exec ctx_ddl.optimize_index (' Ctfs_supply_service_index ', full);

7. User Input keyword Word cut
To achieve this effect, new features of Oracle 10g are needed, and the incoming keywords can be cut first and then retrieved.
First you need to create a policy procedure
exec ctx_ddl.create_policy (' ctfs_policy ', lexer = ' ctfs_lexer ');

A policy procedure named Ctfs_policy is created here, and the parser uses the Ctfs_lexer parser created earlier.
Write an Oracle function to deal with keyword-cutting words:
Create or Replace function P_split_chinese (p_input in VARCHAR2)
return VARCHAR2
As
V_tab Ctx_doc.token_tab;
V_return varchar2 (32767);
Begin
Ctx_doc.policy_tokens (' Ctfs_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;
/

Executes this function in Plsql
SELECT * from Ctfs_buy_service where contains (Keyword_,p_split_chinese (' Java Development '), 1) >0;