Changing CONTEXT (Oracle Text) SYNC parameter without rebuild

From: Bruno Lavoie <bruno.lavoie_at_gmail.com>
Date: Thu, 03 Nov 2011 14:41:48 -0400
Message-ID: <4EB2E06C.3080902_at_gmail.com>



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_at_gmail.com
bl_at_brunol.com

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 03 2011 - 13:41:48 CDT

Original text of this message