How to make better use of Oracle full-text search

There are many ways to search for text in Oracle databases without using the Oracle text function. You can use standard INSTR functions and LIKE operators.

SELECT *FROM mytext WHERE INSTR (thetext, 'Oracle') > 0; SELECT * FROM mytext WHERE thetext LIKE '%Oracle%';

In many cases, it is ideal to use instr and like, especially when searching only small tables. however, using these text locating methods will lead to full table scanning, which is expensive for resources and has very limited search functions. Therefore, when you search massive volumes of text data, we recommend that you use the full-text retrieval function provided by oralce to create full-text retrieval. Step 1. Check and set the database role and check whether the database has the CTXSYS user and CTXAPP role. If you do not have this user or role, it means that the intermedia function is not installed when your database is created. You must modify the database to install this function. By default, the ctxsys user is locked, so you must enable the ctxsys user first. Step 2 grant the execution permission of ctx_ddl to the user who wants to use full-text index under the ctxsys user. For example:

grant execute on ctx_ddl to pomoho;

Step 3 set the lexical analyzer (lexer)

The full-text retrieval mechanism of Oracle is actually very simple. The Oracle patented lexical analyzer (lexer) is used to find all ideographic units (Oracle called term) in the article and record them in a group of tables starting with dr $, at the same time, write down the location, number of times, and hash value of the term. During retrieval, Oracle searches for the corresponding term from this table and calculates the frequency of occurrence. Based on an algorithm, it calculates the score (score) of each document, which is called the 'matching rate '. Lexer is the core of this mechanism, which determines the efficiency of full-text retrieval. Oracle provides different lexer for different languages, and we can usually use three of them:

N basic_lexer: for English. It can separate English words from sentences based on Spaces and punctuations, and automatically treat words that have lost retrieval meaning frequently as 'spam ', such as if, is and so on, with high processing efficiency. However, the lexer has many problems when used in Chinese. Because it only recognizes space and punctuation, and generally does not contain spaces in a Chinese sentence, it regards the entire sentence as a term, in fact, the retrieval capability is lost. Taking the phrase 'Chinese people stood up' as an example, the result of the basic_lexer analysis is only one term, that is, 'Chinese people stood up '. If 'China' is retrieved, NO content is retrieved.

N chinese_vgram_lexer: A specialized Chinese analyzer that supports all Chinese character sets (ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8 ). The analyzer analyzes Chinese sentences in units of words. The Chinese people stood up. This sentence will be analyzed into the following terms: medium, Chinese, Chinese, people, and people ', 'stand up ', get up', 'Come '. It can be seen that this analysis method is easy to implement and can achieve 'all-in-One nets', but the efficiency is unsatisfactory.

N chinese_lexer: this is a new Chinese analyzer that only supports the utf8 character set. As we can see above, the analyzer of chinese vgram lexer does not know commonly used chinese words, so the analysis unit is very mechanical, like the above 'people station ', the term "Start Up" does not appear separately in Chinese. Therefore, this term is meaningless and affects efficiency. The biggest improvement of chinese_lexer is that the analyzer can recognize most of the commonly used Chinese vocabulary, so it can analyze sentences more efficiently. The above two stupid units will not appear again, greatly improving the efficiency. However, it only supports utf8. If your database is in the zhs16gbk character set, you can only use the stupid Chinese vgram lexer.
If no settings are made, Oracle uses the basic_lexer analyzer by default. To specify which lexer to use, perform the following operations:

1. Create a preference under the current user (for example, execute the following statement under the pomoho user)

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

2. Specify the lexer used when creating a full-text index:

CREATE INDEX myindex ON mytable(mycolumn) indextype is ctxsys.context parameters('lexer my_lexer');

In this way, chinese_vgram_lexer is used as the analyzer.

Step 4 Create an index

Use the following syntax to create a full-text index

Create index [schema.] index on [schema.] table (column) indextype is ctxsys. context [ONLINE] LOCAL [(PARTITION [partition] [PARAMETERS ('paramstring')] [, PARTITION [partition] [PARAMETERS ('paramstring')] [PARAMETERS (paramstring)] [PARALLEL n] [UNUSABLE]; Example: Create index ctx_idx_menuname ON pubmenu (menuname) Indextype is ctxsys. context parameters ('lexer my_lexer ')

Step 5 Use Indexes

Full-text index is easy to use. You can use:

Select * from pubmenu where contains (menuname, 'upload image')> 0

Types of full-text indexes

The created Oracle Text index is called domain index, which includes four index types:

L CONTEXT

2 CTXCAT

3 CTXRULE

4 CTXXPATH

You can select either of them based on your application or text data type.

Create a full-text index for multiple fields

In most cases, you need to query records that meet the conditions from multiple text fields. In this case, you need to create a full-text index for multiple fields. For example, you need to query the full-text index from pmhsubjects (topic table) to perform full-text search on subjectname and briefintro, follow these steps:

Ø multi-field index preference is recommended.

Log On With ctxsys and execute:

EXEC ctx_ddl.create_preference ('ctx_idx_subject_pref ', 'Multi _ column_datastore '); Create a field value for preference (Log On With ctxsys) EXEC ctx_ddl.set_attribute ('ctx_idx_subject_pref', 'columns ', 'subjectname, briefintro '); Create a full-text index Create index ctx_idx_subject ON pmhsubjects (subjectname) INDEXTYPE ISctxsys. context parameters ('datastore ctxsys. ctx_idx_subject_pref lexer my_lexer ') Using Indexes Select * from pmhsubjects where contains (subjectname, 'Li yuchun')> 0

Full-text index Maintenance

For CTXSYS. CONTEXT indexes, after the application performs DML operations on the base table, it is necessary to maintain the base table indexes. Index maintenance includes index synchronization and index optimization.

After the index is created, we can check that Oracle automatically generates the following tables under this user: (assuming the index name is myindex ):

DR $ myindex $ I, DR $ myindex $ K, DR $ myindex $ R, DR $ myindex $ N, where the I table is the most important. You can query this table, let's take a look at the following content:

SELECT token_text, token_count FROM dr$i_rsk1$I WHERE ROWNUM <= 20;

The query is not listed here. As you can see, the table stores the term records generated after Oracle analyzes your documents, including the location, number of times, and hash value of the term. When the content of the document changes, you can imagine that the content of this I table should also change accordingly to ensure that the content is correctly retrieved by Oracle during full-text retrieval (because of the so-called full-text retrieval, in fact, the core is to query this table ). In this case, sync and optimize are used.

Sync ):Save the new term to the I table;

Optimize ):Clear the garbage of the I table, mainly to delete the deleted term from the I table.

When an insert, update, or delete operation is performed on the indexed document in the base table, the changes to the base table do not immediately affect the index until the index is synchronized. You can query the CTX_USER_PENDING view to view the corresponding changes. For example:

SELECT pnd_index_name, pnd_rowid, TO_CHAR (pnd_timestamp, 'dd-mon-yyyy hh24: mi: ss') timestamp FROM ctx_user_pending; The output of this statement is similar to the following: PND_INDEX_NAME PND_ROWID TIMESTAMP -------------------------------------------------------------------- MYINDEX AAADXnAABAAAS3SAAC 06--19-1999 15:56:50

Synchronization and Optimization Methods: You can use the ctx_ddl package provided by Oracle to synchronize and optimize indexes.

1. For CTXCAT indexes, Oracle automatically maintains indexes when performing DML operations on the base table. Changes to the document are immediately reflected in the index. CTXCAT is a transactional index.

Index Synchronization

Insert, modify, and delete base tables and synchronize indexes. We recommend that you use sync to synchronize indexes. Syntax:

Ctx_ddl.sync_index ( Idx_name IN VARCHAR2 DEFAULT NULL Memory IN VARCHAR2 default null, Part_name IN VARCHAR2 DEFAULT NULL Parallel_degree in number default 1 ); Idx_name index name Memory specifies the memory required to synchronize indexes. The default value is DEFAULT_INDEX_MEMORY.

Specifying a large memory can accelerate index efficiency and query speed, and the index has less fragments.
Which partition index is synchronized by part_name.
Parallel_degree: Synchronize indexes in parallel. Set the degree of parallelism.

For example:

Synchronous Index myindex: Exec ctx_ddl.sync_index ('myindex ');

Implementation Suggestions: We recommend that you synchronize indexes using oracle jobs.

INDEX OPTIMIZATION

Frequent index synchronization will cause fragmentation of your CONTEXT index. Index fragmentation seriously affects the query response speed. You can regularly optimize indexes to reduce fragmentation, reduce index size, and improve query efficiency.
When the Text is deleted from the table, Oracle Text marks the deleted document but does not immediately modify the index. Therefore, the document information occupies unnecessary space, resulting in additional query overhead. You must optimize the index in FULL mode to delete invalid old information from the index. This process is called garbage processing. When you update and delete table text data frequently, spam is necessary.

Exec ctx_ddl.optimize_index ('myidx', 'full ');
Implementation suggestion: optimize the full-text index every day when the system is idle to improve the Retrieval Efficiency

1. Analysis of Oracle Database management scripts
2. Oracle self-monitoring using measurement baseline Technology
3. Explain how to create and manage users in Oracle