We help IT Professionals succeed at work.

Intermedia

JLS35
JLS35 asked
on
Hi,
i'm using 8.1.7 on NT 4.0 with InterMedia

CREATE TABLE RESUME (
  ID_RESUME     NUMBER (6)    NOT NULL,
  ID_SUJET      NUMBER (6)    NOT NULL,
  ID_LANGUE     VARCHAR2 (2)  NOT NULL,
  TITRE_SUJET   VARCHAR2 (192)  NOT NULL,
  RESUME_SUJET  VARCHAR2 (1024),
  CONSTRAINT PK_RESUME
  PRIMARY KEY ( ID_RESUME ) ) ;

CREATE INDEX titre_im_idx  ON RESUME(titre_sujet) indextype IS ctxsys.context
 
CREATE INDEX resume_im_idx  ON RESUME(resume_sujet) indextype IS ctxsys.context


i'm using Intermedia for full text search
SELECT
  SCORE(1) AS SCORE_RESUME,
  SCORE(2) AS SCORE_TITRE,
  RESUME.TITRE_SUJET,
  RESUME.RESUME_SUJET
FROM
  RESUME
WHERE
  (contains(RESUME.RESUME_SUJET, 'mytext', 1) > 0)
  OR (contains(RESUME.TITRE_SUJET, 'mytext', 2) >0)  

It works well but when i insert, update, delete rows in the RESUME table, the indexes are not  rebuilded...

How to do this ?
Trigger whith deferred procedure ?

Thanx for any idea...
Comment
Watch Question

the intermedia indexes have to be 'actualized' manually or with a process ctxsrv running on your machine(I use this on solaris).

Refer to the oracle docs, this couls help you.

Good luck, Lolo.
Commented:
Hi,

In Intermedia index you have rebuild index wheneever there is a chnages in the table. You can rebuild
index incrementally and full.

You have two options for doing this.

Option 1: COnfigure context server
Option 2: Rebuilding index by sync_index and using dbms_jobs

Option 1: Configure context server.

You have to configure context server by setting up ctxsrv to rebuild index incrementally. Ctxsrv which
is called context server checks rebuild index automatically. It should be up and running always. If
it is down or not running then index will not be rebuild and you have to manully rebuild the index.
The syntax of context server is as follows :-
ctxsrv [-user ctxsys /passwd[@sqlnet_address]]
           [-personality M]
           [-logfile log_name]
           [-sqltrace]

if your server is unix server , you can setup this by using CRON. if your server is NT , either you
have to create a service or you have to start it on DOS and keep the DOS session active always.

Option 2:- Use of CTX_DDL.SYNC_INDEX

CTX_DDL.SYNC_INDEX automatically synochronized index when there is a changes. you should also use CTX_DDL.OPTIMIZE
index to optimize indexes to remove fragementation of intermedia index because of deletion and updation.


The syntex of both the procedures are as follows :-

ctx_ddl.sync_index('SCOTT.ADVANCE_SEARCH');
ctx_ddl.optimize_index('SCOTT.ADVANCE_SEARCH',CTX_DDl.OPTLEVEL_FULL,0);

here advance search is my intermedia index.
0 is  maxtime i defined in ctx_ddl.optimize_index, which perform minimal optimization. you can put
non zero postive number here but i suggest 0 because as the index will grow it will take more time to
optimize for non zero postive values.

I configure option 2 in my production environment.
I created a database procedure  and database logon trigger as

create or replace procedure SCOTT.ContextJoB
as
begin
ctx_ddl.sync_index('SCOTT.ADVANCE_SEARCH');
ctx_ddl.optimize_index('SCOTT.ADVANCE_SEARCH',CTX_DDl.OPTLEVEL_FULL,0);
end;
/  

The above procedure is used for sync and optimize index.

My database trigger is as follows :-

create or replace trigger SCOTTdatabaseJOb
after startup
on database
declare
varwhat varchar2(4000);
varjob    number;
varjobfound number;
begin
select count(*) into varjobfound from sys.dba_jobs where what = 'SCOTT.ContextJoB;';
if varjobfound = 0 then
dbms_job.submit(varjob,'SCOTT.ContextJoB;',sysdate,'sysdate+1/1440');
commit;
else
select job into varjob from sys.dba_jobs where what = 'SCOTT.ContextJoB;';
dbms_job.remove(varjob);
dbms_job.submit(varjob,'SCOTT.ContextJoB;',sysdate,'sysdate+1/1440');
commit;
end if;
end;
/

The above database trigger is a startup trigger which submit a DBMS_JOB to execute SCOTT.COntextJob
procedure.

The DBMS_JOB peridically invoke contextjob procedure and synochronize index.

you have configure job_queue_processes = 4
job_queue_interval = 10
open_links = 4 in the init parameter file if you want to rebuild index by using DBMS_JOBS.

Let me know if you need more info .

Hope it will help

Goodluck
Sam

Explore More ContentExplore courses, solutions, and other research materials related to this topic.