Oracle 12.2 Text Indexes - how to check if configured for synchronous_update vs. asynchronous_update

Jeff K

Oracle 12.2 Text Indexes: Do you know how I'd be able to check whether a text (context) index is configured for synchronous_update vs. asynchronous_update?  I.e., is this parameter (metadata) value  viewable within one of the CTX (or CTX_USER) views?

BluShadow

*** Moderator Note: Question now moved to "Text" space for better question alignment.

Roger Ford-Oracle

You can use ctx_ddl.create_index_script to verify all the settings for the index.  For example in SQL*Plus you could do:

set pagesize 0

set linesize 255

set long 500000

select ctx_ddl.create_index_script('<yourindexname>') from dual;

Look in the PARAMETERS clause near the end. TRANSACTIONAL indicates full transactional index (not normally recommended), SYNC(ON COMMIT) indicates the index is updated immediately at commit time, and SYNC(EVERY ...) indicates a time-based sync. If neither SYNC nor TRANSACTIONAL is mentioned, the index must be manually sync'd with a call to CTX_DDL.SYNC_INDEX.

- Roger

Jeff K

Roger,

I appreciate the reply!  I think you meant ctx_report.create_index_script()? While its output (generated script) is definitely helpful in terms of showing (within the "create index..." statement) the index's "parameters", nowhere therein do I see the literals "synchronous_update" or "asynchronous_update", or some differently worded setting that would infer which is applicable. 

To clarify what I'm seeking, please note the following excerpt from the supplied link:

"When you update the column in a document on which an Oracle Text index is based, that

document is marked as invalid for search operations until index synchronization is performed.

Enabling asynchronous update for an index enables a document to be searchable even though its

index has not yet been synchronized after the index column was updated. Until the index is

synchronized, Oracle Text uses the contents of the old document to answer user queries.

To enable asynchronous update for a Text index:

  ALTER INDEX idx PARAMETERS ('REPLACE METADATA asynchronous_update');

To disable asynchronous update for a Text index:

  ALTER INDEX idx PARAMETERS ('REPLACE METADATA synchronous_update');"

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ccref/oracle-text-SQL-statements-and-operators.html#GUID…

thanks,

Jeff

Roger Ford-Oracle

My apologies, I answered that too quickly. I thought you were asking about the SYNC method.

1/ Yes, I do mean ctx_report

2/ You're absolutely right, create_index_script doesn't dump the (A)SYNCHRONOUS_UPDATE setting for the index. I'll raise a bug for that

3/ We're probably going to need to look in the data dictionary to find the setting. Do you have access to the SYS user?  If so, I'll figure out the necessary SQL to find the setting (it's not trivial).

- Roger

Jeff K

Roger,

No worries. 

re: 3/ We're probably going to need to look in the data dictionary to find the setting. Do you have access to the SYS user?  If so, I'll figure out the necessary SQL to find the setting (it's not trivial)

I don't have access to this DB's SYS user, but I do have access to an amenable DBA whom I can ask to run the TBD "necessary SQL" statement (when you've a chance).

thanks,

Jeff

Roger Ford-Oracle

Here you go. Substitute your username and indexname (in upper case) for ROGER and DOCSINDEX.

select decode( instr(idx_option, 'a'),0,'SYNCHRONOUS_UPDATE',              'ASYNCHRONOUS_UPDATE') as "Index Mode"from ctxsys.dr$indexwhere  idx_id in (  select idx_id from ctxsys.dr$index, sys.user$  where  idx_owner# = user#  and    name      = 'ROGER'  and    idx_name  = 'DOCSINDEX'  )/

To explain that - the IDX_OPTION column in DR$INDEX holds various codes for different index options. 'a' indicates that ASYNCHRONOUS_UPDATE is in use (the values are not documented).

Are you clear as to what ASYNCHRONOUS_UPDATE does?  Just in case you aren't, it basically means that the document will still be searchable after it's been updated but has not yet been SYNC'd. Meaning that a query might return 'incorrect' results, as the results will be for the pre-update text.

With the default setting of SYNCHRONOUS_UPDATE, deletes are immediate but updates don't take effect until SYNC'd, so documents effectively "disappear" between commit and sync.

I logged bug 30102124 for the issue of CTX_REPORT.CREATE_INDEX_SCRIPT not displaying the setting.

Hope that helps.

- Roger

Jeff K

Roger,

Excellent!  I've forwarded the (edited for my user/index) query to the aforementioned DBA. 

He's likely gone for the day, so chances are I'll not hear back from him until Monday (7/29).

Yes, I am clear as to what ASYNCHRONOUS_UPDATE does as well as the caveat you mentioned.

many thanks,

Jeff

Jeff K

Roger,

The synchronous_update/asynchronous_update determination query you supplied worked perfectly!

I have all that I need on this.

thanks,

Jeff

Jeff K

Roger,

The synchronous_update/asynchronous_update determination query you supplied worked perfectly!

I have all that I need on this.

thanks,

Jeff

(I just realized I replied to my own comment earlier.)