2022 – JSON Search Index Synchronization

I was reading this article by Roger Ford and one statement caught my eye “You may notice that if you insert a document but do not commit it, some searches using a search index will not find it, because the index has not yet been synchronized.”

I quickly ran a test for this situation. I created a table with a JSON column in Oracle 21c and added a Search Index.

CREATE TABLE jtable
  ( jcol JSON );

CREATE SEARCH INDEX jindex ON jtable ( jcol ) FOR json;

And created one simple record.

INSERT INTO jtable
  VALUES('{"JTOP":1}');

Then queried it using JSON_EXISTS which favors using Search Indexes.

SELECT *
  FROM jtable
 WHERE JSON_EXISTS(jcol,'$.JTOP');

no rows selected

Here’s Explain Plan proving the Search Index was used.

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0  | SELECT STATEMENT | | 1 | 4114 | 4 (0)| 00:00:01 |
| 1  | TABLE ACCESS BY INDEX ROWID| JTABLE | 1 | 4114 | 4 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | JINDEX | | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------

The Search Index has not been synced yet so the newly added row is not found.

Then I queried using JSON_VALUE which does not always use the Search Index. Will it find the new row?

SELECT *
  FROM jtable
 WHERE JSON_VALUE(jcol,'$.JTOP') IN (1,2);

JCOL
--------------
{"JTOP":1}

Yes it will! And here is the explain plan showing the Search Index was not used.

----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------
| 0  | SELECT STATEMENT | | 1 | 4102 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| JTABLE | 1 | 4102 | 4 (0)| 00:00:01 |
----------------------------------------------------------------

So how do I sync the Search Index? With a  COMMIT statement because that is the default behavior.

COMMIT;

SELECT *
  FROM jtable
 WHERE JSON_EXISTS(jcol,'$.JTOP');

JCOL
--------------
{"JTOP":1}

Now JSON_EXISTS query finds the new row.

Another option is synching the index at intervals by changing the metadata like this.

ALTER INDEX jindex
  PARAMETERS ('replace metadata sync (every "freq=secondly; interval=10")');

Then I insert and commit a row.

INSERT INTO jtable
  VALUES('{"JTOP":2}');

COMMIT;

JSON_VALUE finds both rows right away.

SELECT *
  FROM jtable
 WHERE JSON_VALUE(jcol,'$.JTOP') IN (1,2);

JCOL
--------------------------------------------------------------------------------
{"JTOP":1}
{"JTOP":2}

What about JSON_EXISTS?

SELECT *
  FROM jtable
 WHERE JSON_EXISTS(jcol,'$.JTOP');

JCOL
--------------------------------------------------------------------------------
{"JTOP":1}

Nope. Be patient…try again.

SELECT *
  FROM jtable
 WHERE JSON_EXISTS(jcol,'$.JTOP');

JCOL
--------------------------------------------------------------------------------
{"JTOP":1}

And one last time….

SELECT *
  FROM jtable
  WHERE JSON_EXISTS(jcol,'$.JTOP');

JCOL
--------------------------------------------------------------------------------
{"JTOP":1}
{"JTOP":2}

The Search Index synced after the commit. I assume it occurred at something less than or equal to 10 seconds after the commit but I did not track closely. I’ll investigate this further in the next few posts.

Thanks for reading!