apps-infra

    Forum Stats

  • 3,873,684 Users
  • 2,266,627 Discussions
  • 7,911,600 Comments

Discussions

Need help to decide the trade-off between rebuilding vs syncing & optimizing

DannyS-Oracle
DannyS-Oracle Member Posts: 165
edited Jul 9, 2018 4:07PM in Text

Hi everyone,

I am a beginner user of Oracle Text. Currently I am maintaining a context index with multi_column_datastore in an Apex application (Oracle DB v12c). I am thinking to automate the index update on daily basis, but I am not sure whether I should just rebuild the index (drop and create), or syncing + optimizing.

I had read this interesting article about index synchronization and optimization: Index_Maintenance. The part that I still do not understand is, how am I supposed to choose sync + optimization over simple rebuild? I have not been so lucky to find any study cases online. For example, I want to know how many updates per hour need to happen before I need to consider syncing my index on every commit (or every 1 hour, or 1 week), etc.

To illustrate briefly about my application and its index and updates:

  1. There are about ~20k rows on the indexed table.
  2. There will only be row insertion once a week (about 50-100 new rows inserted per week).
  3. Users be might updating some rows, but only small amount (<1000 rows maximum per day) and happen rarely (1-2 days a week).
  4. Normally, it takes <5 mins to rebuild the index.

Currently I am rebuilding the index on weekly basis after the data insertion which happens once a week. This rebuild happens on low traffic time, so my users never complain about index unavailability during this update. Since rebuilding index is more straightforward, I am leaning towards keeping the current approach; but I am wondering if I am missing any benefits of syncing + optimizing? Any of your feedback will be appreciated, thank you!

Tagged:
DannyS-Oracle

Best Answer

  • Bud Light
    Bud Light Member Posts: 70 Blue Ribbon
    edited Jul 9, 2018 8:11AM Answer ✓

    >>I want to know how many updates per hour need to happen before I need to consider syncing my index on every commit (or every 1 hour, or 1 week), etc.

    If you can be without search for the 5-10 minutes and you want the most optimized index, sure, rebuild it every night.

    It isn't about how many records before you need to sync.  It is how immediate does the new data need to be available to search?  Also how long a commit can take.  Syncing on commit will slow down commits.

    For what you have I would probably sync on commit.  The issue with frequent syncs is fragmentation of the token table.

    If you are concerned about this fragmentation slowing down searches, I used to perform a full optimization every night during slow periods and set maxtime which tells the optimizer when to stop if it isn't done.  If it doesn't complete, it picks back up where it left off on the previous run:

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ccref/CTX_DDL-package.html#GUID-2E079B1-D5CA-4264-B1C0-…

    Optimize doesn't clean up everything but it does a pretty good job.

    For your situation, I would go with a sync on commit.  If you have slow times, a nightly full optimize or at least a weekly.  Then maybe a monthly rebuild just for fun.

    DannyS-Oracle

Answers

  • Bud Light
    Bud Light Member Posts: 70 Blue Ribbon
    edited Jul 9, 2018 8:11AM Answer ✓

    >>I want to know how many updates per hour need to happen before I need to consider syncing my index on every commit (or every 1 hour, or 1 week), etc.

    If you can be without search for the 5-10 minutes and you want the most optimized index, sure, rebuild it every night.

    It isn't about how many records before you need to sync.  It is how immediate does the new data need to be available to search?  Also how long a commit can take.  Syncing on commit will slow down commits.

    For what you have I would probably sync on commit.  The issue with frequent syncs is fragmentation of the token table.

    If you are concerned about this fragmentation slowing down searches, I used to perform a full optimization every night during slow periods and set maxtime which tells the optimizer when to stop if it isn't done.  If it doesn't complete, it picks back up where it left off on the previous run:

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ccref/CTX_DDL-package.html#GUID-2E079B1-D5CA-4264-B1C0-…

    Optimize doesn't clean up everything but it does a pretty good job.

    For your situation, I would go with a sync on commit.  If you have slow times, a nightly full optimize or at least a weekly.  Then maybe a monthly rebuild just for fun.

    DannyS-Oracle
  • DannyS-Oracle
    DannyS-Oracle Member Posts: 165
    edited Jul 9, 2018 4:07PM

    Thank you Bud Light, pretty good suggestions there. Really appreciate it.

This discussion has been closed.
apps-infra