Oracle TEXT, Oracle’s full-text search as an integral part of the Oracle database, is included in all database editions and platforms – on-premises or in Oracle Cloud. It’s directly available without further installation. You can start immediately in a “normal” database schema. If you want to try it out, there are several tutorials and scripts available on Github, in LiveSQL, or in Oracle LiveLabs (see the information in the Further Reading section).

Several changes were made in release 23 to simplify usage: For example, the introduction of Ubiquitous Database Search, which makes it easy to search in different data sources. Only one command is needed to create a self-managed index, and another to add data sources. See also the following two postings: 

But what about the maintenance of an Oracle Text index? Normally, changes to the document table are not immediately visible in the index, but only after the index synchronisation. As Oracle Text users may know, dependent on your need, you can specify different synchronization types (sync type), such as SYNC (MANUAL), SYNC (EVERY “interval-string”), or SYNC (ON COMMIT).  Here is an example, how you can define it: 

create index idx_volltext on document_tab (col_text)
indextype is CTXSYS.CONTEXT
parameters ('<sync type>')

This non-automatic maintenance mode is the behavior in pre-releases of 23. Now, it’s called the manual mode.

In contrast, there is a new automatic maintenance mode in release 23. Instead of manually managing synchronization tasks for your indexes, you can automate CTX_DDL.SYNC_INDEX operations using automatic maintenance, the default mode in release 23 for synchronizing newly created Oracle Text indexes. Indexes with automatic maintenance are synchronized in the background without any user intervention. Special background processes perform index maintenance operations. That provides better scalability of background jobs and enhances query performance. 

Please remember, that there are some limitations. The combination of automatic maintenance with the following parameters is not supported: FAST_QUERY, ASYNCHRONOUS_UPDATE, TRANSACTIONAL, SYNC(ON COMMIT), and SYNC(EVERY). In addition, MAINTENANCE AUTO indexes do not support shadow indexes.

So, let’s illustrate the automatic maintenance behavior in release 23 with a simple example.

SQL> sho parameter compatible

NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
compatible                           string      23.0.0.0

 Let’s create a table with 3000 rows.

SQL> drop table if exists test_txt;
Table dropped. 

SQL> create table test_txt as select rownum as id, -- growing unique number 
    'Frisco' ||rownum textcol1 from dual connect by level<=1000    -- 1000 rows 
    union 
    select rownum as id, -- growing unique number 
    'Melbourne' ||rownum textcol1 from dual connect by level<=1000 -- 1000 rows 
    union 
    select rownum as id, -- growing unique number 
    'Berlin' ||rownum textcol1 from dual connect by level<=1000   -- 10000 rows 
/   
Table created.

Now, let’s create an Oracle Text index. Note I use the default configuration and do not specify any maintenance mode.

SQL> CREATE INDEX ctx_testidx ON test_txt(textcol1) INDEXTYPE IS CTXSYS.CONTEXT;
Index created.

Let’s check the index:

SQL> col idx_sync_memory format a20
SQL> select idx_docid_count, idx_status, idx_sync_type, idx_maintenance_type 
     from ctx_user_indexes;

IDX_DOCID_COUNT IDX_STATUS   IDX_SYNC_TYPE        IDX_MA#
--------------- ------------ -------------------- ------
           3000 INDEXED      MANUAL               AUTO

Notice the index is preconfigured with a combination of automatic maintenance and SYNC(MANUAL). Unlike the regular SYNC (MANUAL) type (where you must manually call CTX_DDL.SYNC_INDEX), here CTX_DDL.SYNC_INDEX is automatically called in the background at optimal intervals.

Let’s test the index …

SQL> select id, textcol1 from test_txt where contains (textcol1, 'fuzzy(Fresco)')>0 fetch first 5 rows only;
        ID TEXTCOL1
---------- -------------------------------------------------
         8 Frisco8
        20 Frisco20
        10 Frisco10
         1 Frisco1
         6 Frisco6

Let’s insert 10000 rows and issue a COMMIT.

SQL> insert into test_txt
     select
     rownum+3000 id, -- growing unique number
     'Munich'||rownum textcol1
     from dual connect by level<=10000 -- 10000 rows
/
10000 rows created.

SQL> commit;
Commit complete.

Let’s check the status…

SQL> select idx_docid_count, idx_status, idx_sync_type, idx_maintenance_type  
     from ctx_user_indexes;

IDX_DOCID_COUNT IDX_STATUS   IDX_SYNC_TYPE        IDX_MA
--------------- ------------ -------------------- ------
          13000 INDEXED      MANUAL               AUTO

Let’s search for Munch instead of Munich and use the fuzzy operator.

SQL> select id, textcol1 from test_txt where contains (textcol1, 'fuzzy(Munch)') > 0 fetch first 5 rows only;
        ID TEXTCOL1
---------- -------------------------------------------------
      3001 Munich1
      3002 Munich2
      3003 Munich3
      3004 Munich4
      3005 Munich5

That’s what we expected.

How does it work ?

In an automatic maintenance mode, background processes maintain indexes. They kick in after a COMMIT and you should see the results of your query almost immediately. The background process checks the queue of pending DMLs every 3 seconds and delegates the work to special background processes, a global resource shared across all PDBs in the system. So on a very quiet system, you should not wait more than 3 sec.

Each SYNC operation consists of separate events (stages) that can concurrently run in the background. You can monitor the different stages in the CTX_USER_BACKGROUND_EVENTS view. A detailed description of the different event stages can be found in List of Maintenance Events in the Oracle text documentation.

In our example after the COMMIT, the result is … 

SQL> select bge_event_type, bge_status, bge_start_time, bge_end_time, bge_num_rows, bge_first_docid, bge_current_docid
     from ctx_user_background_events where bge_table_name='TEST_TXT' order by 3;

BGE_EVENT_TYPE             BGE_STATUS  BGE_START_TIME
-------------------------- ----------- ---------------------------------------------------------------------------
BGE_END_TIME                                                                BGE_NUM_ROWS BGE_FIRST_DOCID BGE_CURRENT_DOCID
--------------------------------------------------------------------------- ------------ --------------- -----------------
SYNC-Mapping               DONE        03-APR-24 02.58.23.341011 PM +02:00
03-APR-24 02.58.23.437254 PM +02:00                                                10000            3001                 0

SYNC-Ranges                DONE        03-APR-24 02.58.23.437418 PM +02:00
03-APR-24 02.58.23.440961 PM +02:00                                                    1            3001                 0#

SYNC-Scheduler             DONE        03-APR-24 02.58.23.441077 PM +02:00
03-APR-24 02.58.23.441197 PM +02:00                                                    1            3001                 0#

SYNC-Postings Serial       DONE        03-APR-24 02.58.23.441312 PM +02:00
03-APR-24 02.58.23.487382 PM +02:00                                                10000            3001             13000

SYNC-Scheduler             DONE        03-APR-24 02.58.23.487539 PM +02:00
03-APR-24 02.58.23.487660 PM +02:00                                                    0               0                 0

SYNC-Writer                DONE        03-APR-24 02.58.23.487690 PM +02:00
03-APR-24 02.58.23.624039 PM +02:00                                                10000            3001                 0

6 rows selected.

You can use the dynamic performance view V$TEXT_WAITING_EVENTS to display historical information about events that are delayed or cannot complete due to errors or contentions.

Upgraded existing indexes continue to use the previously specified method of synchronization. But you can switch between the automatic and manual maintenance modes using ALTER INDEX. This command alters only the synchronization options, and thus you do not need to rebuild the index.

So if want to switch from  SYNC (MANUAL) to MAINTENANCE AUTO, you can issue the following command …

ALTER INDEX ctx_testidx REBUILD PARAMETERS ('REPLACE METADATA MAINTENANCE AUTO');

The next example shows how to switch to the manual mode.

ALTER INDEX ctx_testidx REBUILD PARAMETERS ('REPLACE METADATA MAINTENANCE MANUAL');

You may check the index maintenance mode again with…

SQL> select idx_docid_count, idx_status, idx_sync_type, idx_maintenance_type  
     from ctx_user_indexes;

IDX_DOCID_COUNT IDX_STATUS   IDX_SYNC_TYPE        IDX_MA
--------------- ------------ -------------------- ------
          13000 INDEXED      MANUAL               MANUAL

More examples can be found in the documentation.

Summary

Automatic maintenance and synchronization (SYNC) methods involve processing pending updates, inserts and deletes to the base table. However, the automatic maintenance in release 23 uses an asynchronous maintenance framework to perform SYNC operations in the background. Therefore time-based or manual SYNC operations are eliminated. In an automatic maintenance mode, special background processes perform index maintenance, determine an optimal synchronization interval (based on the DML arrival), and automatically schedule background SYNC operations, as required. Automatic maintenance is the default method for synchronizing Oracle Text indexes in Release 23 and later releases.

Further Readings