I published a video today showing how to use a Text index to pick up search terms from multiple tables to allow a “Google-like” search across your entire application or even your entire database. The video walks through a lot of background on how Text indexes are flexible and can be customised to meet this need, so since it runs to 16 minutes, I thought I’d cut to the chase and show demo from the concluding elements of the video where we take advantage of some cool 19c feature JSON features to make the process even easier.

I’ll start with the familiar EMP and DEPT tables, and our requirement is to show departments based on a keyword search that might refer to data in the DEPT table or the EMP table. (If our intent was to display EMP details, we would build the index on the EMP table, but most of the process below is the same).

SQL> create table dept as select * from scott.dept;

Table created.

SQL> create table emp as select * from scott.emp;

Table created.

I need a procedure that will be used by the Text index to discover the data I want to use when indexing each row in the DEPT table. I’m going to utilise JSON here to build up an JSON array of data that corresponds to the all of the columns in the DEPT and EMP tables for each row in the DEPT table.

SQL> create or replace
  2  procedure  get_details
  3  (rid in rowid, tlob in out nocopy clob) is
  4  begin
  5    select  json_arrayagg(json_object(*))
  6    into    tlob
  7    from    emp e,
  8            dept d
  9    where   d.rowid = rid
 10    and     d.deptno = e.deptno;
 11  end;
 12  /

Procedure created.

Thus each time I pass a rowid that reflects a DEPT row, I’ll return a JSON CLOB that contains both that department details and all of the employees within that department. Now I can let Oracle Text know that this I’ll be using this procedure when I build my index via a preference.

SQL> begin
  2    ctxsys.ctx_ddl.create_preference(user||'.SEARCH_IDX_DSTORE','USER_DATASTORE');
  3    ctxsys.ctx_ddl.set_attribute(user||'.SEARCH_IDX_DSTORE','PROCEDURE',user||'.GET_DETAILS');
  4  end;
  5  /

PL/SQL procedure successfully completed.

In order to build a JSON index, I need to have a JSON column in my table. But DEPT does not have one, so I’ll simply add a new column with a CHECK ( IS JSON ) constraint.

I’ll never put any real data inside that column; it is just there as a JSON placeholder for my JSON index. Now I can go ahead and put my index on that column.

SQL> alter table dept add js varchar2(10);

Table altered.

SQL> alter table dept add constraint js_chk check ( js is json);

Table altered.

SQL> create search index my_search_idx
  2  on dept (js) for json
  3  parameters('sync (on commit) datastore SEARCH_IDX_DSTORE');

Index created.

I’m ready now for some JSON_TEXTCONTAINS queries using this index. For example, if I search for 7369, then I get department 20 returned. This is because there is a row in the EMP table for employee 7369, and this employee is assigned to department 20. Similarly, employee KING works in department 10.

SQL> select * from dept
  2  where json_textcontains(js, '$', '7369');

    DEPTNO DNAME          LOC           JS
---------- -------------- ------------- ----------
        20 RESEARCH       DALLAS

SQL> select * from dept 2 where json_textcontains(js,'$', 'KING');

    DEPTNO DNAME          LOC           JS
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK

Because it is JSON, I can interrogate the elements with a more granular level as well. I can search for KING but only within the ENAME elements where they will exist, and obviously will not exist in the SAL element.

SQL> select * from dept
  2  where json_textcontains(js, '$.ENAME', 'KING');

    DEPTNO DNAME          LOC           JS
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK

SQL> select * from dept
  2  where json_textcontains(js, '$.SAL', 'KING');

no rows selected

Lets do some DML now and commit those changes. I’ve now got a row in the EMP table with a salary of 2999. Will our index find it?

SQL> insert into emp (empno, ename, job, sal,deptno )
  2  values (1000,'TEST','SALES',2999,30);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select * from dept
  2  where json_textcontains(js, '$', '2999');

no rows selected.

Even though the index is defined as SYNC ON COMMIT, and I did indeed commit those changes, the query has not returned any rows. This is because the database knows that the index is defined on the JS column, and detected that this column was not touched by the DML we just issued. So no index maintenance occurred. Thus we need to give the index a nudge by updating the JS column as part of our transaction.

SQL> update dept set js=js
  2  where deptno = 30;

1 row updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select * from dept
  2  where json_textcontains(js, '$', '2999'); 

    DEPTNO DNAME          LOC           JS
---------- -------------- ------------- ----------
        30 SALES          CHICAGO

Now our changes are seen because the JS column was updated. Typically you would take care of this with a trigger if needed, but that requires a little bit of extra thought which is covered in detail in the video below.

But there you have it! Keyword search across multiple tables to give your customers a fast and flexible experience when they are looking for data.