Oracle Full-text retrieval implementation

1 ----1 prepare to unlock ctxsys users and authorize-----2 3 --Unlock Ctxsys4 Alter Userctxsys account unlock;5 --Modify the Ctxsys password (if the Ctxsys user expires, you need to reset the password)6 Alter UserCtxsys identified byCtxsys;7 8 --grant permissions to oa this user--oa can be changed to actually use the user9 Grant Execute  onCtx_ddl toOA;Ten  One  A ----2 ready to work create analyzers, word breakers, filter phrases----- - --Creating analyzers - execCtx_ddl.create_preference ('My_lexer','Chinese_vgram_lexer'); the --creating a word-cutting device - execCtx_ddl. Create_policy ('My_policy', LEXER= 'My_lexer'); - --Create a word breaker (function) - Create or Replace functionP_split_chinese (p_inputinch varchar2)  + return varchar2  as  - V_tab Ctx_doc. Token_tab;  +V_returnVARCHAR2(32767);  A begin  atCtx_doc. Policy_tokens ('My_policy', P_input,v_tab);  -  forIinch 1.. V_tab.CountLoop -V_return:=V_return|| ',' ||V_tab (i). token; - EndLoop; - return LTRIM(V_return,',');  - End;  in / - --test function P_split_chinese to SelectP_split_chinese ('Chongqing, China') fromdual; +  - --Create a filter phrase the execCtx_ddl.create_stoplist ('my_stoplist');  * --Add the words you want to filter into the my_stoplist, and the words will not participate in the search $ execCtx_ddl.add_stopword ('my_stoplist','Co., Ltd.');Panax Notoginseng  -   the  +  A -----3 Create an index (using the word breaker, the filter phrase you created earlier)----- the Create IndexMy_test_index onTest_table (Text) Indextype isCtxsys. CONTEXT parameters ('lexer my_lexer stoplist my_stoplist'); +   -  $ ----4, querying (using indexes)----- $ --do not use a word breaker (rownum<=1000 This limitation is adjusted according to the actual situation) - SelectScore (1), T.*  fromTest_table Twhere contains(Text,' SystemAdministrator',1)>0  andRowNum<= + Order  byScore (1)desc; - --use a word breaker (rownum<=1000 This limitation is adjusted according to the actual situation) the SelectScore (1), T.*  fromTest_table Twhere contains(Text, P_split_chinese ('system Administrator'),1)>0  andRowNum<= + Order  byScore (1)desc; - Wuyi   the  - ----5, index maintenance---- Wu --Synchronize the index (to re-execute this statement after new data or modify the data, otherwise the new data will not participate in the search) - execCtx_ddl.sync_index ('My_test_index'); About --Optimizing Indexes $ execCtx_ddl.optimize_index ('My_test_index',' Full'); -  -  

Oracle Full-text retrieval implementation