Re: Changing CONTEXT (Oracle Text) SYNC parameter without rebuild
- From: Manuela Atoui <manuelaout@xxxxxxxxxxxxxx>
- To: bruno.lavoie@xxxxxxxxx
- Date: Fri, 4 Nov 2011 10:44:33 +0200
Dear Mr. Lavoi, dear list members,
I'm trying to answer you questions in three different paragraphs:
1. - alter index...replace metadata:
Your statement is correct according to the cited documentation below:
SQL> alter index i_ft_qstr_titre parameters ('REPLACE METADATA
SYNC(EVERY "SYSDATE+15/1440")')
http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/csql.htm#i996850
alter index... does the same as ctx_ddl.replace_metadata
http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/cddlpkg.htm#sthref1605
Do not use the whole original parameter string.
<quote>
Note: parameter_string
Specify the parameter string to be passed to ALTER INDEX. This
must begin with 'REPLACE METADATA'.
</quote>
- For manual Oracle Text synchronisation, schedule a job every n
minutes (allows you maximum flexibility, you can always change the
interval)
exec CTX_DDL.REPLACE_INDEX_METADATA('idx_name','REPLACE METADATA SYNC
(MANUAL)');
- or schedule hard coded, e.g. every 5 minutes
exec CTX_DDL.REPLACE_INDEX_METADATA('idx_name','REPLACE METADATA SYNC
(every "SYSDATE+5/1440")');
2. to check index health use ctx_report.describe index. This procedure
also shows you the current setting of the index metadata.
http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/crptpkg.htm#sthref1960
3. To get a clearer picture of the needed interval for index
synchronisations, query the view ctx_pending:
http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/aviews.htm#sthref2637
Hope that helps, if you have further questions you can also send me an
email directly.
Kind regards
Manuela Atoui
Expert Data Systems s.a.r.l.
CTO, Senior Oracle DBA
Berytech Technological Pole
Mar Roukoz, Mkalles, Beirut, Lebanon
+961 4 533 040 - +961 76 730 406
Email: manuela@xxxxxxxxx
www.ed-sys.eu
On Thu, Nov 3, 2011 at 8:41 PM, Bruno Lavoie <bruno.lavoie@xxxxxxxxx> wrote:
> 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
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
