One of the new features in Oracle Database 23ai is a new PL/SQL interface called DBMS_SEARCH.
DBMS_SEARCH builds on existing Oracle Text functionality to create an index using multiple schema objects (both tables as well as views) as data sources for the index – so search across the entire database domain as opposed to a single table.
A DBMS_SEARCH index is basically a type of Oracle Text index designed for JSON data.
We can use both Oracle Text CONTAINS clause as well as JSON-specific JSON_TEXTCONTAINS for conducting search against the index.
Official documents also refer to it as “Ubiquitous database search”.
We can use the CREATE_INDEX procedure of DBMS_SEARCH to create a search index.
For example:
exec DBMS_SEARCH.CREATE_INDEX(index_name=>’PERSON_INDX’);
The index is automatically maintained as data in the base table(s) or view(s) change.
Let us look at an example of using the 23ai DBMS_SEARCH package.
Create the demo users
SQL> create user TIGER identified by Oracle_4U;
User created.
SQL> grant dba to tiger;
Grant succeeded.
SQL> create user SCOTT identified by Oracle_4U;
User created.
SQL> grant dba to scott;
Grant succeeded.
Create the demo tables
SQL> create table tiger.person (person_id number, person_name varchar2(20), description varchar2(4000));
Table created.
SQL> create table scott.person (person_id number, person_name varchar2(20), description varchar2(4000));
Table created.
SQL> alter table tiger.person add constraint pk_person primary key (person_id);
Table altered.
SQL> alter table scott.person add constraint pk_person primary key (person_id);
Table altered.
Load demo data
SQL> insert into scott.person values (1,'GAVIN','THIS IS GAVIN IN SCOTT.PERSON TABLE');
1 row created.
SQL> insert into scott.person values (2,'SOORMA','THIS IS SOORMA IN SCOTT.PERSON TABLE');
SQL> commit;
Commit complete.
SQL> insert into tiger.person values (1,'GAVIN','THIS IS GAVIN IN TIGER.PERSON TABLE');
1 row created.
SQL> insert into tiger.person values (2,'SOORMA','THIS IS SOORMA IN TIGER.PERSON TABLE');
1 row created.
SQL> COMMIT;
Commit complete.
Create the index using DBMS_SEARCH.CREATE_INDEX
SQL> BEGIN
DBMS_SEARCH.CREATE_INDEX('PERSON_INDX');
END;
/
PL/SQL procedure successfully completed.
SQL> select object_name,object_type from user_objects where object_name='PERSON_INDX';
OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
PERSON_INDX TABLE
PERSON_INDX TABLE PARTITION
PERSON_INDX TABLE PARTITION
PERSON_INDX TABLE PARTITION
PERSON_INDX INDEX
PERSON_INDX INDEX PARTITION
PERSON_INDX INDEX PARTITION
PERSON_INDX INDEX PARTITION
8 rows selected.
Note: a table with the same name as the index is also created.
The table is created with two JSON columns DATA and METADATA.
The METADATA column uniquely identifies each row of a particular data source, using the OWNER, SOURCE, and KEY JSON values.
SQL> desc PERSON_INDX
Name Null? Type
----------------------------------------- -------- ----------------------------
METADATA NOT NULL JSON
DATA JSON
OWNER VARCHAR2(128)
SOURCE VARCHAR2(128)
KEY VARCHAR2(1024)
SQL> select index_type,owner,ityp_owner,table_name from dba_indexes where index_name='PERSON_INDX';
INDEX_TYPE OWNER ITYP_OWNER TABLE_NAME
--------------------------- -------------------- ------------------------------ ------------
DOMAIN SCOTT CTXSYS PERSON_INDX
Add data sources to the index
The ADD_SOURCE procedure of DBMS_SEARCH adds the tables to the earlier created index as data sources.
SQL> BEGIN
DBMS_SEARCH.ADD_SOURCE('PERSON_INDX', 'SCOTT.PERSON');
DBMS_SEARCH.ADD_SOURCE('PERSON_INDX', 'TIGER.PERSON');
END;
/
PL/SQL procedure successfully completed.
Check the indexed data
Data added from the source tables is not stored in the PERSON_INDX table.
Only metadata is stored in a binary JSON column.
To view the data in human-readable format we can use the JSON_SERIALIZE function along with DBMS_SEARCH.GET_DOCUMENT procedure.
SQL> SELECT json_serialize(DBMS_SEARCH.GET_DOCUMENT('PERSON_INDX',metadata)) FROM person_indx;
JSON_SERIALIZE(DBMS_SEARCH.GET_DOCUMENT('PERSON_INDX',METADATA))
----------------------------------------------------------------------------------------------------------------
{"SCOTT":{"PERSON":{"PERSON_ID":1,"PERSON_NAME":"GAVIN","DESCRIPTION":"THIS IS GAVIN IN SCOTT.PERSON TABLE"}}}
{"SCOTT":{"PERSON":{"PERSON_ID":2,"PERSON_NAME":"SOORMA","DESCRIPTION":"THIS IS SOORMA IN SCOTT.PERSON TABLE"}}}
{"TIGER":{"PERSON":{"PERSON_ID":1,"PERSON_NAME":"GAVIN","DESCRIPTION":"THIS IS GAVIN IN TIGER.PERSON TABLE"}}}
{"TIGER":{"PERSON":{"PERSON_ID":2,"PERSON_NAME":"SOORMA","DESCRIPTION":"THIS IS SOORMA IN TIGER.PERSON TABLE"}}}
SQL> SELECT JSON_SERIALIZE(metadata) FROM PERSON_INDX;
JSON_SERIALIZE(METADATA)
---------------------------------------------------------------
{"OWNER":"SCOTT","SOURCE":"PERSON","KEY":{"PERSON_ID":1}}
{"OWNER":"SCOTT","SOURCE":"PERSON","KEY":{"PERSON_ID":2}}
{"OWNER":"TIGER","SOURCE":"PERSON","KEY":{"PERSON_ID":1}}
{"OWNER":"TIGER","SOURCE":"PERSON","KEY":{"PERSON_ID":2}}
SQL> SELECT json_serialize(DBMS_SEARCH.GET_DOCUMENT('PERSON_INDX', metadata) )
FROM person_indx p
WHERE p.metadata.OWNER = 'SCOTT'
AND p.metadata.SOURCE = 'PERSON'
AND p.metadata.KEY.PERSON_ID = 1;
JSON_SERIALIZE(DBMS_SEARCH.GET_DOCUMENT('PERSON_IDX',METADATA))
--------------------------------------------------------------------------------
{"SCOTT":{"PERSON":{"PERSON_ID":1,"PERSON_NAME":"GAVIN","DESCRIPTION":"THIS IS G
AVIN IN SCOTT.PERSON TABLE"}}}
SQL> col owner format a20
SQL> col source format a20
SQL> select p.metadata.OWNER, p.metadata.SOURCE, count(*)
from person_indx p
group by p.metadata.OWNER, p.metadata.SOURCE; 2 3
OWNER SOURCE COUNT(*)
-------------------- -------------------- ----------
"SCOTT" "PERSON" 2
"TIGER" "PERSON" 2
Execute queries against the indexed data
We can execute queries against the index using both the JSON_TEXTCONTAINS and CONTAINS operators
SQL> SELECT json_serialize(metadata) FROM person_indx
WHERE CONTAINS (data, 'GAVIN') > 0;
JSON_SERIALIZE(METADATA)
---------------------------------------------------------------
{"OWNER":"SCOTT","SOURCE":"PERSON","KEY":{"PERSON_ID":1}}
{"OWNER":"TIGER","SOURCE":"PERSON","KEY":{"PERSON_ID":1}}
SQL> SELECT json_serialize(metadata) FROM person_indx
WHERE CONTAINS (data, 'gavin') > 0;
JSON_SERIALIZE(METADATA)
---------------------------------------------------------------------
{"OWNER":"SCOTT","SOURCE":"PERSON","KEY":{"PERSON_ID":1}}
{"OWNER":"TIGER","SOURCE":"PERSON","KEY":{"PERSON_ID":1}}
SQL> select json_serialize(metadata) FROM person_indx
where JSON_TEXTCONTAINS (data, '$.SCOTT.PERSON', 'GAVIN');
JSON_SERIALIZE(METADATA)
--------------------------------------------------------------
{"OWNER":"SCOTT","SOURCE":"PERSON","KEY":{"PERSON_ID":1}}
SQL> SELECT json_serialize(DBMS_SEARCH.GET_DOCUMENT('PERSON_INDX',metadata)) FROM person_indx
WHERE CONTAINS (data, 'gavin') > 0;
JSON_SERIALIZE(DBMS_SEARCH.GET_DOCUMENT('PERSON_INDX',METADATA))
----------------------------------------------------------------------------------------------------------------
{"SCOTT":{"PERSON":{"PERSON_ID":1,"PERSON_NAME":"GAVIN","DESCRIPTION":"THIS IS GAVIN IN SCOTT.PERSON TABLE"}}}
{"TIGER":{"PERSON":{"PERSON_ID":1,"PERSON_NAME":"GAVIN","DESCRIPTION":"THIS IS GAVIN IN TIGER.PERSON TABLE"}}}
