Oracle C # a simple example of implementing Oracle Text full-Text search

Oracle C #ImplementationOracle Text full-Text searchThe simple example is what we will introduce in this article ,. A simple example of using C # to implement full-Text retrieval of Oracle Text in. NET is designed to get a preliminary understanding of the general steps of full-Text retrieval of Oracle. You can expand on this basis for further learning. Hope to help you.

The full-text retrieval procedure for Oracle is as follows:

(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 full-text Oracle 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 or delete the table), 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 is  
  
  
begin  
  
  
execute 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.

 
 

  
  
declare  
  
  
v_job number;  
  
  
begin  
  
  
Dbms_Job.Submit  
  
  
(  
  
  
job => v_job,  
  
  
what => 'sync;',  
  
  
next_date => sysdate, /* default */  
  
  
interval => 'sysdate + 1/720' /* = 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.

Note:

(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) The full-text search under 11g is better.

The above is all the content of the Oracle C # full-Text search example for implementing Oracle Text. We will introduce it here, and hope this introduction will help you gain some benefits!