Oracle text (Full-text search)

Oracle text (Full-text search)

View database Tutorials Related Information select * from Nls_database_parameters
1. Simple Application
1.1 If you want to use Full-text search, the current Oracle user must have the Ctxapp role

--Create a user
--create user Textsearch identified by Textsearch;
/**
Give the user three roles, one of which is the Ctxapp role,
So that the user can use the procedure associated with Full-text search
*/
Grant Connect,resource,ctxapp to Textsearch;
/

Log on using the created user
Sql> Conn Textsearch
Enter Password: **********
is connected.

1.2 Create the data table for Full-text Search, prepare the data
--drop table Textdemo;
CREATE TABLE Textdemo (
ID number NOT NULL primary key,
Book_author varchar2 (a),--author
Publish_time Date,--Published
Title VARCHAR2 (,--)
Summary of Book_abstract VARCHAR2 (,--)
Path VARCHAR2 (200)--Paths
);
Commit

INSERT into Textdemo values (1, ' Gang ', to_date (' 2008-10-07 ', ' yyyy-mm-dd '), ' Move Castle ', ' the story happened in Europe at the end of 19th century, the kind and lovely Sophie was cursed by the Wicked Witch, From a 18-year-old girl to a 90-year-old mother-in-law, she accidentally walks into a moving castle outside the town, it is said that its owner HAL to absorb the girl's soul for joy, but things are not so scary as people say, strange Hal actually took Sophie, two people in the four-foot mobile castle began a wonderful common life, A love story interwoven with love and pain, joy and sadness unfolds in the flames of war ', ' E:textsearchmoveingcastle.doc ';

INSERT into Textdemo values (2, ' Mor Bekmanbetov ', to_date (' 2008-10-07 ', ' yyyy-mm-dd '), ' bullets turn ', ' This is from the Russian director Timo Bekmanbetov's film, which has been in North America since the end of June, has made more than 300 million dollars in box-office receipts around the world. After the release in Asia has also won the Japanese, South Korea and other place of the box office title. Although many netizens have contacted the film through various channels before, but believe the film with the super Cool audio-visual effect on the big screen, still can attract a large number of fans to the cinema to join in. ', ' e:textsearchcatch.pdf ');

INSERT into Textdemo values (3, ' Yuan Quan ', to_date (' 2008-10-07 ', ' yyyy-mm-dd '), ' starring Wu and Yuan Quan appear ', ' The Movie Dream ' was filmed in Shanghai Fun Place, starring Wu and Yuan Quan. Because it is shooting late at night, so there is not too many fans to notice, gave the cast a very clean shooting environment, standing on the streets of the Yuan Quan head down, in the cold night looks really some like a female ghost, creepy. ', ' e:textsearchdream.txt ');

Commit

1.3 Creating an index on the summary field

/*
* CREATE INDEX, use default parameter
*/
--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) Build the table and load the text.

(2) indexing is established. If you want to configure an Oracle index, you can configure it before indexing, such as changing the lexical analyzer. You can view the configuration of Oracle Full-text retrieval in the following SQL statement:

SELECT * from Ctx_preferences;
(3) SQL query.

(4) Index maintenance: Synchronization and optimization.

Authorized
Users who perform the full text must have Ctxapp roles or Ctxsys users, and CTX_DDL package execution permissions.

(1) using the SYS user to grant SCOTT user Ctxapp role, the command is as follows:

GRANT Ctxapp to SCOTT;
(2) Use Ctxsys user to give SCOTT user authorization ctx_ddl The execution permission of the package, the command is as follows:

GRANT EXECUTE on Ctx_dll to SCOTT;

Creating tables, adding records, and indexing
The following SQL statement and job are executed under the SCOTT user. First, execute the following SQL statement, create the table DOCS, insert two records, and then create the index Doc_index after the commit.

DROP TABLE DOCS;  CREATE TABLE DOCS (ID number PRIMARY key,text VARCHAR2 (80)); Insert INTO docs values (1, ' The ' the ' "); INSERT into docs values (2, ' the second Doc ');  COMMIT; CREATE INDEX Doc_index on DOCS (text) Indextype is Ctxsys. context;
Then, execute the query and 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 (sql STR, connstr);D atatable dt = new DataTable ();d A. 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 reflect this change, which requires synchronization and optimization of the index. Oracle provides CTX server to complete synchronization and optimization, or it can be done with the following job.

Sync 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 ') '; Ync;
Optimization
Clears the garbage of I table and deletes the term that have been deleted from the I table.

Declarev_job number;begindbms_job.submit (Job => v_job,what => ' sync; ', next_date => sysdate,/* Default * * Interval => ' sysdate + 1/720 '/* = 1 day/(hrs * min) = 2 mins */);D Bms_job.run (v_job);
Where the I table is the dr$doc_index$i table. After the user builds the 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 contents of this table.

Description
(1) This paper is to fully implement Oracle Full-text search in Oracle 9i and 10g environments, including setting up tables and indexes, synchronizing and optimizing;

(2) The SQL statement for Full-text search is "select ID from DOCS WHERE CONTAINS (text, '%first% ') >0";

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

(4) of which, "CONTAINS (TEXT, '%first% ') >0", in Oracle 9i and 10g and 11g under different;

(5) The recent project from Oracle 10g to 11g, in the Full-text Search, Oracle 10g code, under 11g to retrieve the results;

(6) Initially, the difference between Oracle 9i and 10g and 11g is that, under 9i and 10g, if "%" is not used, it is accurate retrieval, otherwise it is fuzzy retrieval. Under 11g, there is no "%" at all;

(7) In addition, under 9i and 10g, you can use such as: CONTAINS (text, '%first% and%second% ') >0, Full-text Search, but under 11g, is not possible, to write separately, such as:

CONTAINS (text, '%first% ') >0 and CONTAINS (text, '%second% ') >0;
(8) Feel the full text search under 11g better