Home » Server Options » Text & interMedia » [Oracle 9i] : Synchronize Oracle Text index on CLOB field
[Oracle 9i] : Synchronize Oracle Text index on CLOB field [message #384798] Thu, 05 February 2009 04:12 Go to next message
daringa
Messages: 2
Registered: February 2009
Location: France
Junior Member
Hi,

I have a problem with ORACLE Text index.
I want to synchronize an ORACLE Text Index based on a CLOB field.
I searched some help in this forum and i found this :

http://www.orafaq.com/forum/m/271059/0/?srch=ctx_ddl.sync_index#msg_271059

But it doesn't work Sad.

What I do:

1)I first create an index on my CLOB field:
CREATE INDEX IDX_EXTRACT_TITRE
ON TABLE_TITLE (Titre) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('DATASTORE DS_EXTRACT_TITRE LEXER LX_EXTRACT_TITRE FILTER FILTER_EXTRACT_TITRE STORAGE STORAGE_EXTRACT_TITRE WORDLIST WORDLIST_EXTRACT_TITRE');


2) I create a trigger :
CREATE OR REPLACE TRIGGER TU_TABLE_TITLE
AFTER INSERT OR UPDATE OR DELETE ON TABLE_TITLE
DECLARE
  v_job NUMBER;
BEGIN
  IF deleting THEN
    DBMS_JOB.SUBMIT(v_job, 'ctx_ddl.optimize_index(''IDX_EXTRACT_TITRE
'',''FULL'');', SYSDATE);
  ELSE
    DBMS_JOB.SUBMIT(v_job, 'ctx_ddl.sync_index(''IDX_EXTRACT_TITRE');', SYSDATE);
  END IF;
END;
/
show errors;
/


3) I test my trigger with an update
 UPDATE TABLE_TITLE
    SET titre = 'BLA BLO BLI'
  WHERE key=<keyValue>;
 
COMMIT;
 
SELECT count(*) FROM TABLE_TITLE WHERE CONTAINS(TITRE, 'BLA') > 0  (returns 0 line)


The job is created and seems to work (no error in oracle log)
Maybe I forget one step or something else.
Anyone have an idea ?

Thanks


Re: [Oracle 9i] : Synchronize Oracle Text index on CLOB field [message #384970 is a reply to message #384798] Thu, 05 February 2009 23:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You probably haven't waited long enough for the job to finish running before checking the results. Notice in the example below that before executing dbms_lock.sleep the count is 0 and after it is 1. With a larger index it may take longer.

SCOTT@orcl_11g> CREATE TABLE table_title
  2    (key    NUMBER,
  3  	title  CLOB)
  4  /

Table created.

SCOTT@orcl_11g> INSERT INTO table_title VALUES (1, 'test title')
  2  /

1 row created.

SCOTT@orcl_11g> CREATE INDEX idx_extract_title
  2  ON table_title (title)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER tu_table_title
  2    AFTER INSERT OR UPDATE OR DELETE ON table_title
  3  DECLARE
  4    v_job NUMBER;
  5  BEGIN
  6    IF deleting THEN
  7  	 DBMS_JOB.SUBMIT
  8  	   (v_job,
  9  	    'ctx_ddl.optimize_index(''IDX_EXTRACT_title'',''FULL'');',
 10  	    SYSDATE);
 11    ELSE
 12  	 DBMS_JOB.SUBMIT
 13  	   (v_job,
 14  	    'ctx_ddl.sync_index(''IDX_EXTRACT_title'');',
 15  	    SYSDATE);
 16    END IF;
 17  END tu_table_title;
 18  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> UPDATE table_title
  2  SET    title = 'BLA BLO BLI'
  3  WHERE  key = 1
  4  /

1 row updated.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> SELECT COUNT (*)
  2  FROM   table_title
  3  WHERE  CONTAINS (title, 'BLA') > 0
  4  /

  COUNT(*)
----------
         0

SCOTT@orcl_11g> EXEC DBMS_LOCK.SLEEP (5)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT COUNT (*)
  2  FROM   table_title
  3  WHERE  CONTAINS (title, 'BLA') > 0
  4  /

  COUNT(*)
----------
         1

SCOTT@orcl_11g> 


Re: [Oracle 9i] : Synchronize Oracle Text index on CLOB field [message #390973 is a reply to message #384970] Tue, 10 March 2009 09:34 Go to previous messageGo to next message
daringa
Messages: 2
Registered: February 2009
Location: France
Junior Member
I have 50000 lines in my table; it's not very important. But the contents can be important (It takes 40 minutes to rebuild the index when i dropped/created).

Does It take the same time to synchronize Shocked ?

Cause a data problem can pollute the index synchronization ?

Thx !
Re: [Oracle 9i] : Synchronize Oracle Text index on CLOB field [message #391003 is a reply to message #390973] Tue, 10 March 2009 11:22 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If your index has been recently recreated, then synchronizing should not take long, although the more rows you have inserted and/or updated prior to committing, the longer it will take. Synchronizing just adds rows to the domain index table, so that the new data is immediately searchable. This causes some index fragmentation. The more inserts and updates, the more fragmentation. So, you need to optimize or rebuild or drop and recreate periodically to eliminate the fragementation. You might not want to optimize after every delete. You might want to remove that from the trigger. That was something that was appropriate for the situation of the user in the thread that you extracted the example from. It may be waiting for the latest optimize to finish before starting the synchronize.

I would do some testing to determine just how long the synchronize after update is taking. I would start by dropping and recreating the index first, then doing an update of one row. Then run the query after every minute or so, to see how long it takes. If it seems like it is too long, then you may want to check memory settings and such to see if that helps speed up the synchronization time. If your data is available for searching only a few minutes after updating, considering the complexity of a context index with all of the parameters you have, such as datastore and lexer and filter, I would call that quite reasonable. I don't know what your filter is. If it is a third-party filter, it could be slowing things down.

Also, you would probably get better performance if you upgraded to 10g or 11g.
Previous Topic: Recreating Fuzzy index for each insert
Next Topic: MULTI_STOPLIST using WORLD_LEXER
Goto Forum:
  


Current Time: Thu Mar 28 17:20:29 CDT 2024