Oracle Text (full-Text search)

Oracle Text (full-Text search)

View database tutorial Information select * from nls_database_parameters
1. Simple Application
1.1 to use full-text search, the current ORACLE user must have the CTXAPP role

-- Create a user
-- Create user textsearch identified by textsearch;
/**
Assign the user three roles, one of which is the CTXAPP role,
This allows you to use PROCEDURE related to full-text search.
*/
Grant connect, resource, ctxapp to textsearch;
/

Use the created user to log on
SQL> conn textsearch
Enter the password :**********
Connected.

1.2 create a data table for full-text search and prepare the data
-- Drop table textdemo;
Create table textdemo (
Id number not null primary key,
Book_author varchar2 (20), -- Author
Publish_time date, -- Release date
Title varchar2 (400), -- title
Book_abstract varchar2 (2000), -- Abstract
Path varchar2 (200) -- path
);
Commit;

Insert into textdemo values (1, 'gong qijun', to_date ('1970-10-07', 'yyyy-mm-dd'), 'Move bucket ', 'The story happened in Europe at the end of the 19th century. The kind and lovely Sophie was cursed by a vicious witch, and changed from an 18-year-old girl to a 90-Year-Old Mother-in-law, alone and helpless, she accidentally walked out of the town's mobile castle. It is said that its host Hal is happy to learn from the girl's soul, but things are not as terrible as people say, halle, the odd nature, took Sufi in, and the two men started a wonderful life together in the four-legged mobile Castle, A love story that is intertwined with love and pain, joy and sorrow quietly expands in the war ', 'e: textsearchmoveingcastle.doc ');

Insert into textdemo values (2, 'mo · Beckman batthov ', to_date ('2017-10-07', 'yyyy-mm-dd'), 'bullet turns ', 'The global box office revenue has exceeded $ June since the film was launched in North America at the end of 0.3 billion, directed by Russian director Tim Beckman beov. After its release in Asia, it also won the box office championship in Japan, South Korea and other places. Although many netizens have come into contact with this film through various channels, I believe that the film will still attract a large number of fans to the cinema thanks to its cool audiovisual effect on the screen. ', 'E: textsearchcatchance ');

Insert into textdemo values (3, 'yuan quan', to_date ('2017-10-07', 'yyyy-mm-dd'), 'starring Wu yanzu and Yuan Quan ', 'The movie "ruimeng" was filmed in Shanghai tonglefang, starring Wu yanzu and Yuan Quan. Because it was shot late at night, fans did not notice that they gave the cast a clean shooting environment, and Yuan Quan, standing on the street, bowed his head, in the cold night, it looks a little scary. ', 'E: textsearchdream.txt ');

Commit;

1.3 create an index on the summary Field

/*
* Create an index and use the default parameters.
*/
-- Drop index demo_abstract;
Create index demo_abstract on textdemo (book_abstract)
Indextype is ctxsys. context
-- Parameters ('datastore ctxsys. default_datastore filter ctxsys. auto_filter ')
;
Commit;

(1) create a table and load the text.

(2) create an index. If you want to configure an Oracle index, you can configure it before creating an index, for example, changing the lexical analyzer. You can use the following SQL statement to view the configuration of Oracle full-text search:

SELECT * FROM CTX_PREFERENCES;
(3) SQL query.

(4) index maintenance: synchronization and optimization.

 

Authorization
You must have the CTXAPP role, CTXSYS user, and CTX_DDL package execution permissions.

(1) Use SYS to grant SCOTT the CTXAPP role. The command is as follows:

Grant ctxapp to scott;
(2) Use the CTXSYS user to authorize the SCOTT user to execute the CTX_DDL package. The command is as follows:

Grant execute on CTX_DLL to scott;
 

Create tables, add records, and index
The following SQL statements and jobs are executed under the SCOTT user. First, execute the following SQL statement to create the table DOCS, insert two records, submit the statement, and create the index doc_index.

Drop table docs; create table docs (id number primary key, text VARCHAR2 (80); insert into docs VALUES (1, 'the first doc '); insert into docs VALUES (2, 'The second doc '); COMMIT; create index doc_index on docs (text) indextype is ctxsys. CONTEXT;
Then execute the query. The C # code is as follows:

String connStr = "Data Source = ora9; uid = scott; pwd = tiger; unicode = true"; string sqlStr = "select id from docs where contains (TEXT, '% FIRST %')> 0 "; OracleDataAdapter da = new OracleDataAdapter (sqlStr, connStr); DataTable dt = new DataTable (); da. fill (dt); Response. write (dt. rows [0] [0]. toString ());
 

Synchronization and Optimization
When the table DOCS changes (insert, delete), the index must be able to respond to this change, which requires synchronization and optimization of the index. Oracle provides the ctx server for synchronization and optimization, or you can use the following job to complete.

Sync
Save the new term to the I table.

Create or replace procedure sync isbeginexecute immediate 'alter index doc_index rebuild online' | 'parameters (''sync '')'; execute immediate 'alter index doc_index rebuild online' | 'parameters (''optimize full maxtime unlimited') '; end sync;
Optimization
Clear table I junk and delete deleted terms from Table I.

Declarev_job number; beginDbms_Job.Submit (job => v_job, what => 'sync; ', next_date => sysdate, /* default */interval => 'sysdate + 100'/* = 1 day/(24 hrs * 30 min) = 2 mins */); Dbms_Job.Run (v_job); end;
Here, the I table is the dr $ doc_index $ I table. After you create an index, Oracle automatically creates four tables, dr $ doc_index $ I, dr $ doc_index $ k, dr $ doc_index $ n, and dr $ doc_index $ r. You can use the SELECT statement to view the content of this table.

 

Description
(1) This article fully implements full-text retrieval of Oracle in Oracle 9i and 10g environments, including creating tables and indexes for synchronization and optimization;

(2) The SQL statement for full-TEXT search is "SELECT ID FROM DOCS WHERE CONTAINS (TEXT, '% FIRST %')> 0 ";

(3) "> 0" is required for a valid Oracle SQL statement, because Oracle SQL does not support Boolean return values of functions;

(4) "CONTAINS (TEXT, '% FIRST %')> 0" is different in Oracle 9i and 10g and 11g;

(5) recently, the project was changed from Oracle 10 Gb to 11 GB. During full-text retrieval, the code under Oracle 10 Gb could not be retrieved under 11 GB;

(6) It is preliminarily believed that the difference between Oracle 9i and 10g is that if "%" is not used under 9i and 10g, it is a precise search; otherwise, it is a fuzzy search. In 11g, "%" is not used at all ";

(7) In addition, in 9i AND 10g, full-TEXT retrieval can be performed using CONTAINS (TEXT, '% FIRST % AND % second %')> 0, but in 11g, it is not allowed. It should be written separately, for example:

CONTAINS (TEXT, '% FIRST %')> 0 and contains (TEXT, '% second %')> 0;
(8) I feel that the full-text search under 11g is better