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!