Re: Changing CONTEXT (Oracle Text) SYNC parameter without rebuild
- From: Manuela Atoui <manuelaout@xxxxxxxxxxxxxx>
- To: Bruno Lavoie <bruno.lavoie@xxxxxxxxx>
- Date: Fri, 4 Nov 2011 18:31:10 +0200
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@xxxxxxxxx
www.ed-sys.eu
On Fri, Nov 4, 2011 at 2:26 PM, Bruno Lavoie <bruno.lavoie@xxxxxxxxx> 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@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:
