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@ed-sys.eu
www.ed-sys.eu
On Fri, Nov 4, 2011 at 2:26 PM, Bruno Lavoie wrote:Thanks, see my comments bellow.
Le 2011-11-04 04:44, Manuela Atoui a écrit :
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#sthref19603. 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#sthref2637Hope 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@ed-sys.eu
www.ed-sys.eu
On Thu, Nov 3, 2011 at 8:41 PM, Bruno Lavoie<bruno.lavoie@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@gmail.com
bl@brunol.com
--
http://www.freelists.org/webpage/oracle-l --
http://www.freelists.org/webpage/oracle-l
