[ACCEPTED]-How to sync and optimize an Oracle Text index?-oracle-text
What do you mean by "not automatically 10 updated"?
The index can be synchronized 9 on commit or periodically.
Create index ... on ... INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC ( ON COMMIT)')
Create index ... on ... INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS 'SYNC (EVERY "SYSDATE+1/24")')
I you don't need 8 real-time search accuracy our DBA recommended 7 to sync the index periodically, say each 6 2 min. If you can afford to do it overnight, then 5 even better. What is best depends on your 4 load and the size of the document.
These 3 links can probably provide you with more 2 information:
For DBA advice, maybe serverfault 1 is better?
I think 'SYNC EVERY' option, as described 9 in previous answer only available in Oracle 8 10g or newer. If you're using older version 7 of Oracle you would have to run sync operation 6 periodically. For example, you can create 5 following stored procedure:
CREATE OR REPLACE
Procedure sync_ctx_indexes
IS
CURSOR sql1 is select distinct(pnd_index_owner||'.'||pnd_index_name) as index_name from ctx_pending;
BEGIN
FOR rec1 IN sql1 LOOP
ctx_ddl.sync_index(rec1.index_name);
END LOOP;
END;
and then schedule 4 it run via DBMS_JOB:
DBMS_JOB.SUBMIT(job_id, 'sync_ctx_indexes;', SYSDATE, 'SYSDATE + 1/720');
As for index optimization, following 3 command can be used (also can be scheduled 2 with DBMS_JOB or via cron):
alter index my_index rebuild online parameters('optimize full maxtime 60');
There is also 1 CTX_* package with similar function available.
Putting this here as an update for Oracle 7 12C users. If you use the index in real 6 time mode, then it keeps items in memory, and 5 periodicially pushes to the main tables, which 4 keeps fragmentation down and enables NRT 3 search on streaming content. Here's how 2 to set it up
exec ctx_ddl.drop_preference ( 'your_tablespace' );
exec ctx_ddl.create_preference( 'your_tablespace', 'BASIC_STORAGE' );
exec ctx_ddl.set_attribute ( 'your_tablespace', 'STAGE_ITAB', 'true' );
create index some_text_idx on your_table(text_col) indextype is ctxsys.context PARAMETERS ('storage your_tablespace sync (on commit)')
this will set up the index in 1 NRT mode. It's pretty sweet.
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.