Oracle Full-Text Search creation Script example

--Create a full-text index

Grant execute on CTX_DDL to username;--using a different account for username authorization
exec ctx_ddl.create_preference (' my_lexer ', ' chinese_lexer ');--Create Chinese_lexer lexical analyzer (Chinese lexical parser)
exec ctx_ddl.create_preference (' My_filter ', ' charset_filter ');--Create character filter
exec ctx_ddl.set_attribute (' My_filter ', ' CharSet ', ' ZHS16GBK ');--Set Filter character encoding
CREATE INDEX Idx_tablename_colname on tablename (colname) Indextype is ctxsys.context parameters (' lexer my_lexer ');-- Create an index on TableName

--Use full-text search:

SELECT * FROM TableName where contains (col, ' Beijing ');

--Full-text index synchronization

For example: Sync index myindex:exec ctx_ddl.sync_index (' Myindex '); Implementation recommendations: It is recommended that the index be synchronized through Oracle's job

--Full-text indexing optimization

    Frequent index synchronization will result in fragmentation of your context index. Index fragmentation severely affects the response speed of the query. You can optimize indexes regularly to reduce fragmentation, reduce index size, and improve query efficiency.    when the text is deleted from the table, the Oracle text tag deletes the document, but does not immediately modify the index. As a result, the document information takes up unnecessary space, resulting in additional overhead for querying.
    You must optimize the index in full mode to remove invalid old information from the index. This process is called garbage disposal. Garbage processing is necessary when you frequently update the table text data and delete the operation.

Oracle Full-Text Search creation Script example