Changing CONTEXT (Oracle Text) SYNC parameter without rebuild

Hello,
I'm trying to change the SYNC parameter on already existing 
ctxsys.context index without rebuilding the whole thing. The real index 
in production is quite big and is currently configured to be sync(on 
commit). This setting causes some latency when inserting en-masse. 
Regarding to Oracle documentation, it's stated that only one session can 
sync at a time, this serialize concurrent commits.

To relax things up, we want to change it to automatic sync at regular 
intervals of, say 15, minutes.

I drove some tests to see what happens to the index configs when 
modifying only the SYNC metadata.
SQL> -- Create original index
SQL> create index i_ft_qstr_titre
on questionnaire (titre_questionnaire)
indextype is ctxsys.context parameters('LEXER ICU_LEXER_QSTR_TITRE 
WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON 
COMMIT)')
Index created.
-- Show info
SQL> select idx_name, idx_status, idx_type, idx_sync_type, 
idx_sync_interval, idx_sync_jobname
from ctx_user_indexes
where idx_name like 'I_FT_QSTR_TITRE'

IDX_NAME                  IDX_STATUS                IDX_TYPE   
IDX_SYNC_TYPE   IDX_SYNC_INTERV IDX_SYNC_JOBNAME
------------------------- ------------------------- ---------- 
--------------- --------------- -------------------------
I_FT_QSTR_TITRE           INDEXED                   CONTEXT    ON COMMIT

SQL> select index_name, parameters
from user_indexes
where index_name like 'I_FT_QSTR_TITRE'

INDEX_NAME                PARAMETERS
------------------------- 
------------------------------------------------------------------------------------------------------------------------
I_FT_QSTR_TITRE *LEXER ICU_LEXER_QSTR_TITRE WORDLIST 
ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON COMMIT)*

-- Alter SYNC parameter via ALTER INDEX
-- SYNTAX : ALTER INDEX index_name PARAMETERS (paramstring)
SQL> alter index i_ft_qstr_titre parameters ('REPLACE METADATA 
SYNC(EVERY "SYSDATE+15/1440")')
Index altered.

-- Show info
SQL> select idx_name, idx_status, idx_type, idx_sync_type, 
idx_sync_interval, idx_sync_jobname
from ctx_user_indexes
where idx_name like 'I_FT_QSTR_TITRE'

IDX_NAME                  IDX_STATUS                IDX_TYPE   
IDX_SYNC_TYPE   IDX_SYNC_INTERV IDX_SYNC_JOBNAME
------------------------- ------------------------- ---------- 
--------------- --------------- -------------------------
I_FT_QSTR_TITRE           INDEXED                   CONTEXT    
AUTOMATIC       SYSDATE+15/1440 DR$I_FT_QSTR_TITRE$J

SQL> select index_name, parameters
from user_indexes
where index_name like 'I_FT_QSTR_TITRE'

INDEX_NAME                PARAMETERS
------------------------- 
------------------------------------------------------------------------------------------------------------------------
I_FT_QSTR_TITRE *REPLACE METADATA SYNC(EVERY "SYSDATE+15/1440")*

As you can see, it replaces the whole PARAMETERS column returned by 
user_indexes view rather than changing only the sync metadata part.
Is it normal?
Does it negatively change other crucial parameters like the wordlist, 
lexer, stoplist, etc.?
Why dows the «REPLACE METADATA» is now present in PARAMETERS?

I know it looks like this, but should I use instead an alter index with 
the whole original parameters string?
/alter index i_ft_qstr_titre parameters ('REPLACE METADATA LEXER 
ICU_LEXER_QSTR_TITRE WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST 
CTXSYS.EMPTY_STOPLIST *SYNC(EVERY "SYSDATE+15/1440")*');/

New output from queries:
SQL> select idx_name, idx_status, idx_type, idx_sync_type, 
idx_sync_interval, idx_sync_jobname
from ctx_user_indexes
where idx_name like 'I_FT_QSTR_TITRE'

IDX_NAME                  IDX_STATUS                IDX_TYPE   
IDX_SYNC_TYPE   IDX_SYNC_INTERV IDX_SYNC_JOBNAME
------------------------- ------------------------- ---------- 
--------------- --------------- -------------------------
I_FT_QSTR_TITRE           INDEXED                   CONTEXT    
AUTOMATIC       SYSDATE+1/24    DR$I_FT_QSTR_TITRE$J

SQL> select index_name, parameters
from user_indexes
where index_name like 'I_FT_QSTR_TITRE'

INDEX_NAME                PARAMETERS
------------------------- 
------------------------------------------------------------------------------------------------------------------------
I_FT_QSTR_TITRE           REPLACE METADATA LEXER ICU_LEXER_QSTR_TITRE 
WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST  
SYNC(EVERY  "SYSDATE+1/24")

*ENV Details:*
Oracle Database 10g Enterprise Edition Release 10.2.
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Product
NLSRTL Version 10.2.0.5.0 - Production

Thanks in advance
Bruno Lavoie
bruno.lavoie@xxxxxxxxx
bl@xxxxxxxxxx



--
http://www.freelists.org/webpage/oracle-l

Other related posts: