[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