Simple configuration for Oracle 9i full-text retrieval

Simple configuration for Oracle 9i full-text retrieval
1. Prerequisites
All Oracle 9i instances are supported by default, as shown in the following figure.
The ctxsys user and ctxapp role exist in the system,
The listener supports full-text retrieval, as shown below:
# Listener. ora network configuration file: D:/Oracle/ora92/Network/admin/listener. ora
# Generated by Oracle configuration tools.

Listener =
(Description_list =
(Description =
(Address_list =
(Address = (Protocol = IPC) (Key = extproc0 ))
)
(Address_list =
(Address = (Protocol = TCP) (host = server) (Port = 1521 ))
)
)
)

Sid_list_listener =
(Sid_list =
(Sid_desc =
(Sid_name = plsextproc)
(ORACLE_HOME = D:/Oracle/ora92)
(Program = EXTPROC)
)
(Sid_desc =
(Global_dbname = server)
(ORACLE_HOME = D:/Oracle/ora92)
(Sid_name = server)
)
)
2. Set the syntax analyzer
In ctxsys, basic_lexer is used by default. It provides better support for English. To support Chinese, make the following settings.
Begin ctx_ddl.create_preference ('My _ lexer ', 'Chinese _ vgram_lexer'); end;
3. Instance
In the page_desc and link_tile of the search_catalog table under user search, full-text retrieval is required, as shown below:
Create index index_text on search_catalog (page_desc) indextype is ctxsys. context parameters ('lexer ctxsys. my_lexer ');
Create index index_title on search_catalog (link_title) indextype is ctxsys. context parameters ('lexer ctxsys. my_lexer ');

// You can modify the index bucket. The simplest method is to create a bucket in the default bucket of another user.
Grant create any index to "Search ";
Grant alter any index to "Search ";
Create index shiyq_index.index_text on search_catalog (page_desc) indextype is ctxsys. context parameters ('lexer ctxsys. my_lexer ');
Create index shiyq_index.index_title on search_catalog (link_title) indextype is ctxsys. context parameters ('lexer ctxsys. my_lexer ');
// You can customize the storage space

Synchronization (when the raw data changes, the index will not be automatically rebuilt, so manual synchronization is required)
Alter index index_text rebuild online parameters ('sync ');
Alter index index_title rebuild online parameters ('sync ');
Or
Alter index shiyq_index.index_text rebuild online parameters ('sync ');
Alter index shiyq_index.index_title rebuild online parameters ('sync ');

4. Results
Select T. *, T. rowid from search_catalog t
Where instr (T. page_desc, 'health')> 0

Select * From search_catalog where contains (page_desc, 'health')> 0

5. Addendum:
In this case, when a word is retrieved, the full-text search of the two fields is used and connected using and or. In this case, the performance is relatively low,
And easily cause CPU bottlenecks, so you can combine the two fields into one, such as adding link_title to the end of page_desc, and then
SQL
Select * From search_catalog t where contains (T. link_title, 'health')> 0 and contains (T. page_desc,
'Health')> 0
Change
Select * From search_catalog t where contains (T. page_desc, 'health')> 0
If you search for multiple words, you can use logical symbols, such as logical and (and or &), logical or (or |), logical not (not or ~), Yes
Organized in parentheses
For example, search data that contains "health", "medicine", or "product", but does not contain "Hai Hong"
Select * From search_catalog t where contains (T. page_desc, '(Health & (medicine | product ))~ Haihong')> 0

Reference to the Jade plane flying dragon masterpiece, this article is very good, I write very simple, have a problem can go to see http://epub.itpub.net/4/1.htm,