How to search full Text using Oracle Text

The following articles describe how to correctly use Oracle Text for full-Text retrieval, therefore, the like syntax is used to query the full text of the law, which is slow. Therefore, the full-text retrieval function is added to the original system.

Full-Text retrieval relies on Oracle Text. First, ensure that the Oracle Text Component is installed in the database. Then create an index

SQL code

Add an index for fields in the legal full text

 
 

  
  
create index idx_flqw_nr on flqw(nr) indextype is ctxsys.context; 
 
 

Add an index to the legal terms Field

 
 

  
  
create index idx_fltk_nr on fltk(nr) indextype is ctxsys.context; 
 
 

Because the ctxsys. context index used by Oracle Text is not automatically maintained, the index needs to be updated regularly and optimized, and the number of index optimization times is slightly less.

SQL code

Update Index

 
 

  
  
exec ctx_ddl.sync_index('idx_flqw_nr');  
  
  
exec ctx_ddl.sync_index('idx_fltk_nr');  
 
 

Optimize Indexes

 
 

  
  
exec ctx_ddl.optimize_index('idx_flqw_nr','full');  
  
  
exec ctx_ddl.optimize_index('idx_fltk_nr','full');  
 
 

You can also write the updated index and optimization into a job, which can be run on a regular basis. The job should be created in the same directory as the user.

First, create a corresponding stored procedure.

SQL code

Grant the flyy user the permission to perform full-text indexing during the stored procedure.

 
 

  
  
GRANT EXECUTE ANY PROCEDURE TO flyy; 
 
 

Stored Procedure for updating indexes

 
 

  
  
CREATE OR REPLACE PROCEDURE flyy.sync_index  
  
  
AS  
  
  
BEGIN  
  
  
ctxsys.ctx_ddl.sync_index ('idx_flqw_nr');  
  
  
ctxsys.ctx_ddl.sync_index ('idx_fltk_nr');  
 
 

END;

Optimize the index storage process

 
 

  
  
CREATE OR REPLACE PROCEDURE flyy.optimize_index  
  
  
AS  
  
  
BEGIN  
  
  
ctx_ddl.optimize_index ('idx_flqw_nr', 'full');  
  
  
ctx_ddl.optimize_index ('idx_fltk_nr', 'full');  
  
  
END;  
 
 

Create a scheduled job.

SQL code

Update indexes every 15 minutes

 
 

  
  
VARIABLE job1 number;  
  
  
BEGIN  
  
  
DBMS_JOB.submit (:job1, 'sync_index;', SYSDATE, 'sysdate+1/24/4');  
  
  
END;  
 
 

Optimize indexes and run once a day

After tests, optimizing indexes may lead to deadlocks. It is best to optimize indexes manually and use them with caution!

 
 

  
  
VARIABLE job1 number;  
  
  
BEGIN  
  
  
DBMS_JOB.submit (:job1, 'optimize_index;', SYSDATE, 'sysdate+1');  
  
  
END;  
 
 

Run the preceding job and query the job ID.

SQL code

Find the corresponding JOB

 
 

  
  
SELECT job, schema_user, INTERVAL, what FROM user_jobs; 
 
 

Start the job based on the query results.

SQL code

Start job

 
 

  
  
EXEC dbms_job.run(41);  
 
 

Is the job number queried above.