Re: Changing CONTEXT (Oracle Text) SYNC parameter without rebuild

It seems that ALTER INDEX clause modify the user_indexes, but that your technique with CTX_DDL.REPLACE_INDEX_METADATA replaces only things in Oracle Text metadata & dictionary.

The pending question is: what happens when we need to export a schema containing a modified text index?
Does export (expdp, dbms_metadata) handle well these incoherent changes? I would like to know what is happening with dbms_metadata and expdp respectively, prevent rather than fixing. If someone know the answer it is good, but if not i'll test it and get back about it. Maybe I need to search a bit about it on net and MOS.

Many Thanks
Bruno Lavoie

Le 2011-11-04 12:31, Manuela Atoui a écrit :
> Dear Mr. Lavoie, dear list members,
>
> in a nutshell, user_indexes is not updated when you change the Oracle
> text index parameters, please see test case below.
>
> 0. create a test user for Oracle Text
> $ sqlplus / as sysdba
>
> SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 4 17:53:24 2011
>
> Copyright (c) 1982, 2009, Oracle. All rights reserved.
>
>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing options
>
> SQL> create user textuser identified by textuser
> default tablespace users
> temporary tablespace temp; 2 3
>
> User created.
>
> SQL> grant connect, resource, ctxapp to textuser;
>
> Grant succeeded.
>
> 1. set some configuration values in Oracle Text
> SQL> conn textuser/textuser
> Connected.
> SQL> set timing on
> SQL>set serveroutput on size 1000000
> SQL> exec ctx_ddl.create_preference('stem_preference', 'BASIC_WORDLIST');
> exec ctx_ddl.set_attribute('stem_preference', 'STEMMER', 'DERIVATIONAL');
>
>
> Elapsed: 00:00:00.02
> SQL>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.02
> SQL>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.01
>
> 2. create a test table and an Oracle context index with synchronize 'on commit'
> SQL> create table quick(
> quick_id number
> constraint quick_pk primary key,
> text varchar2(80)
> ); 2 3 4 5
>
> Table created.
>
> Elapsed: 00:00:00.02
> SQL> create index quick_text on quick(text)
> indextype is ctxsys.context
> parameters ('wordlist stem_preference sync(on commit)') ; 2 3
>
> Index created.
>
> Elapsed: 00:00:00.14
>
> 3. insert one row, commit ==> Oracle Text index gets automatically synchronised
> SQL> insert into quick (quick_id, text)
> values(1, 'english stem test for african nation'); 2
>
> 1 row created.
>
> Elapsed: 00:00:00.04
> SQL> commit;
>
> Commit complete.
>
> Elapsed: 00:00:00.04
>
> 4. Check the setting 'synchronise on commit' with Oracle Text view
> ctx_user_indexes, user_indexes and the procedure
> ctx_report.describe_index:
> 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 'QUICK_TEXT'; 2 3 4
>
> IDX_NAME
> --------------------------------------------------------------------------------
> IDX_STATUS IDX_TYPE
> ------------------------------------ ---------------------
> IDX_SYNC_TYPE
> ------------------------------------------------------------
> IDX_SYNC_INTERVAL
> --------------------------------------------------------------------------------
> IDX_SYNC_JOBNAME
> --------------------------------------------------------------------------------
> QUICK_TEXT
> INDEXED CONTEXT
> ON COMMIT
>
> IDX_NAME
> --------------------------------------------------------------------------------
> IDX_STATUS IDX_TYPE
> ------------------------------------ ---------------------
> IDX_SYNC_TYPE
> ------------------------------------------------------------
> IDX_SYNC_INTERVAL
> --------------------------------------------------------------------------------
> IDX_SYNC_JOBNAME
> --------------------------------------------------------------------------------
>
>
>
>
> Elapsed: 00:00:00.01
> SQL> select index_name, parameters
> from user_indexes
> where index_name like 'QUICK_TEXT'; 2 3
>
> INDEX_NAME
> --------------------------------------------------------------------------------
> PARAMETERS
> --------------------------------------------------------------------------------
> QUICK_TEXT
> wordlist stem_preference sync(on commit)
>
>
> Elapsed: 00:00:00.01
> SQL> set long 64000
> SQL> set pages 0
> SQL> set heading off
> SQL> set feedback off
> SQL> spool outputfile
> SQL> select ctx_report.describe_index('quick_text') from dual;
> ===========================================================================
> INDEX DESCRIPTION
> ===========================================================
> ================
> index name: "TEXTUSER"."QUICK_TEXT"
> index id: 1106
> index type: context
>
> base table: "TEXTUSER"."QUICK"
> primary key column: QUICK_ID
> text column: TEXT
> text column type: VARCHAR2(80)
> language column:
> format column:
> charset column:
> Query Stats Enabled: NO
> sync type: on commit
>
>
> status: INDEXED
> full optimize token:
> full optimize count:
> docid count: 1
> nextid: 2
>
> ===================================================================
> ========
> INDEX OBJECTS
> ===========================================================================
> datastore: DIRECT_DATASTORE
>
> filter: NULL_FILTER
>
> section group: NULL_SECTION_GROUP
>
> lexer: BASIC_LEXER
>
> wordlist: BASIC_WORDLIST
> stemmer: DERIVATIONAL
>
> stoplist: BASIC_STOPLIST
> stop_word: Mr
> .....
> stop_word: yours
>
> storage: BASIC_STORAGE
> r_table_clause: lob (data) store as (cache)
>
> i_index_clause: compress 2
>
>
>
> Elapsed: 00:00:00.03
> SQL> spool off
> SQL>
>
> Conclusion: ctx_user_indexes, user_indexes and
> ctx_report.describe_index all report for column parameters 'on
> commit'.
>
> ==========================================================
> 5. let's change the sync behavior with CTX_DDL.REPLACE_INDEX_METADATA
>
> SQL> set serveroutput on
> SQL> exec CTX_DDL.REPLACE_INDEX_METADATA('quick_text','REPLACE
> METADATA SYNC(MANUAL)');
>
> PL/SQL procedure successfully completed.
>
> 6. Check again the current setting with 'synchronise manual' with
> Oracle Text view ctx_user_indexes, user_indexes and the procedure
> ctx_report.describe_index:
> 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 'QUICK_TEXT'; 2 3 4
>
> IDX_NAME
> --------------------------------------------------------------------------------
> IDX_STATUS IDX_TYPE
> ------------------------------------ ---------------------
> IDX_SYNC_TYPE
> ------------------------------------------------------------
> IDX_SYNC_INTERVAL
> --------------------------------------------------------------------------------
> IDX_SYNC_JOBNAME
> --------------------------------------------------------------------------------
> QUICK_TEXT
> INDEXED CONTEXT
> MANUAL
>
> IDX_NAME
> --------------------------------------------------------------------------------
> IDX_STATUS IDX_TYPE
> ------------------------------------ ---------------------
> IDX_SYNC_TYPE
> ------------------------------------------------------------
> IDX_SYNC_INTERVAL
> --------------------------------------------------------------------------------
> IDX_SYNC_JOBNAME
> --------------------------------------------------------------------------------
>
> Conclusion: ctx_user_indexes reflects the accurate current setting
> with 'synchronise manual'
>
>
> SQL> select index_name, parameters
> from user_indexes
> where index_name like 'QUICK_TEXT'; 2 3
>
> INDEX_NAME
> --------------------------------------------------------------------------------
> PARAMETERS
> --------------------------------------------------------------------------------
> QUICK_TEXT
> wordlist stem_preference sync(on commit)<--- not updated !!!
>
> Conclusion: user_indexes still reports the old, now wrong parameter
> 'sync on commit' !!
>
> SQL> set long 64000
> set pages 0
> set heading off
> set feedback off
> spool outputfile
> select ctx_report.describe_index('quick_text') from dual;
> spool offSQL> SQL> SQL> SQL> SQL>
> ===========================================================================
> INDEX DESCRIPTION
> ===========================================================
> ================
> index name: "TEXTUSER"."QUICK_TEXT"
> index id: 1106
> index type: context
>
> base table: "TEXTUSER"."QUICK"
> primary key column: QUICK_ID
> text column: TEXT
> text column type: VARCHAR2(80)
> language column:
> format column:
> charset column:
> Query Stats Enabled: NO
> sync type: manual
>
>
> status: INDEXED
> full optimize token:
> full optimize count:
> docid count: 1
> nextid: 2
>
> ======================================================================
> =====
> INDEX OBJECTS
> =====================================================
> ======================
> datastore: DIRECT_DATASTORE
>
> filter: NULL_FILTER
>
> section group: NULL_SECTION_GROUP
>
> lexer: BASIC_LEXER
>
> wordlist: BASIC_WORDLIST
> stemmer: DERIVATIONAL
>
> stoplist: BASIC_STOPLIST
> stop_word: Mr
> ....
> stop_word: yours
>
> storage: BASIC_STORAGE
> r_table_clause: lob (data) store as (cache)
> i_index_clause: compress 2
>
> Conclusion:
> ctx_user_indexes, and ctx_report.describe_index all report for column
> parameters 'on commit' BUT user_indexes is not updated, the value in
> parameter is still 'on commit'.
>
>
> 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_at_ed-sys.eu
> www.ed-sys.eu
>
> On Fri, Nov 4, 2011 at 2:26 PM, Bruno Lavoie<bruno.lavoie_at_gmail.com> wrote:
>> Thanks, see my comments bellow.
>>
>> Le 2011-11-04 04:44, Manuela Atoui a écrit :
>>> 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>
>> So why «do not user the whole parameter strings», is it normal to see my
>> complete parameters list disapear from user_indexes.parameters field?
>> In case of export, I presume that it will not transport all the necessary
>> index settings.
>>
>> I've also read that ctx_ddl.replace_metadata is not the preferred way as
>> opposed to alter index statement.
>>
>> Briefly, what is bugging in my mind is that user_indexes.parameters is not
>> accurate...
>>> - 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_at_ed-sys.eu
>>> www.ed-sys.eu
>>>
>>> On Thu, Nov 3, 2011 at 8:41 PM, Bruno Lavoie<bruno.lavoie_at_gmail.com>
>>> 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_at_gmail.com
>>>> bl_at_brunol.com
>>>>
>>>>
>>>>
>>>> --
>>>> http://www.freelists.org/webpage/oracle-l
>>>>
>>>>
>>>>
>>

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