Summary of Oracle full-text index

Steps:

1. Set the lexical analyzer (lexer)

Ensure that the login user has the corresponding permission and ctxapp role with the ctxsys user, or directly use the ctxsys user to log on. The statement is as follows:

Begin

Ctx_ddl.create_preference ('Context _ lexer ', 'Chinese _ vgram_lexer ');

End;

Here, the blue part is the lexical analyzer name (you can set it by yourself), and the red part is the lexical analyzer (Optional: basic_lexer, chinese_vgram_lexer, and chinese_lexer). The default value is basic_lexer)

2. Create a full-text index for the corresponding table fields (here, the ggxh field in the ylqx_reg table is used as an example)

Log On with the user corresponding to the table and use the following statement to create a full-text index

Create index context_ind_ylqx_reg on ylqx_reg (ggxh) indextype is
Ctxsys. context parameters ('lexer context_lexer ')

You can use

Select token_text, token_count from Dr $ context_ind_ylqx_reg $ I

Statement to view the analysis results of the lexical analyzer

3. automatic update and optimization of created indexes are implemented through two jobs, as shown in the following figure (ensure that the login user has access to the ctx_ddl package and can log on to the system user to authorize the user)

 

 

1/24. automatically update the index job. For example, in interval, sysdate + (/4) indicates updating every 15 minutes)

 

 

 

. Automatically optimize the index job, for example, (sysdate + 1 in interval indicates optimization once every day)

 

 

 

 

Other Related Materials

The following is the full-text Oracle index information.

In Oracle, the score Calculation Method in full-text index is as follows:

The standard Salton formula used to assign each score is as follows:

3f (1 + Log (n/n ))

 

Where...

 

F = the frequency of the search term in the document,

N = the total number of "rows in the table" (or documents in the Library), and

N = the number of rows (or arguments) which contain the search term.

 

Score can be between 0 and 100, but the top-scoring document in the query will not

 

Necessarily have a score of 100 -- scoring is relative, not absolute. This means that scores

 

Are not compatible indexes SS indexes, or even indexes SS queries on the same index.

Create a new mytest table and test the score value. If:

The mytest table is as follows:

 

The score obtained when querying "China" is as follows:

 

 

 

Use commands

Begin

 

Ctx_ddl.create_preference ('My _ lexer2 ', 'Chinese _ lexer ');

 

End;

Create index myindex on mytest (name) indextype is ctxsys. context parameters ('lexer my_lexer2 ')

The generated keyword information is as follows:

 

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/lixiaowen2007/archive/2008/12/27/3624541.aspx