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:
- There are about ~20k rows on the indexed table.
- There will only be row insertion once a week (about 50-100 new rows inserted per week).
- Users be might updating some rows, but only small amount (<1000 rows maximum per day) and happen rarely (1-2 days a week).
- 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!