Forum Stats

  • 3,827,859 Users
  • 2,260,833 Discussions
  • 7,897,400 Comments

Discussions

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

Jeff K
Jeff K Member Posts: 8 Blue Ribbon
edited Jul 29, 2019 11:52AM in Text

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?

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    edited Jul 26, 2019 3:01AM

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

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Jul 26, 2019 4:00AM

    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
    Jeff K Member Posts: 8 Blue Ribbon
    edited Jul 26, 2019 12:19PM

    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
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Jul 26, 2019 12:49PM

    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
    Jeff K Member Posts: 8 Blue Ribbon
    edited Jul 26, 2019 1:04PM

    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
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Jul 26, 2019 3:03PM

    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
    Jeff K Member Posts: 8 Blue Ribbon
    edited Jul 26, 2019 5:25PM

    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
    Jeff K Member Posts: 8 Blue Ribbon
    edited Jul 29, 2019 11:49AM

    Roger,

    The synchronous_update/asynchronous_update determination query you supplied worked perfectly!

    I have all that I need on this.

    thanks,

    Jeff

  • Jeff K
    Jeff K Member Posts: 8 Blue Ribbon
    edited Jul 29, 2019 11:52AM

    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.)