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.
I am using Oracle XE 21c
I am trying your search from EMP (EMPLOYEES) and DEPT (DEPARTMENTS) from the Examples
I am trying to run the search for employee_Id 114
select * from dept where json_textcontains(js, ‘$.EMPLOYEE_ID’, ‘114’);
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID JS
————- —————————— ———- ———– ———-
30 Purchasing 114 1700
When I try
select * from dept where json_textcontains(js, $’, ‘114’);
as in your post then I get
SQL Error: ORA-00911: invalid character
When I try
select * from dept where json_textcontains(js, ‘$’, ‘114’);
How do I do a wildcard query. For example I want to search for all phone numbers with 515
The phone numbers are stored as xxx.xxx.xxxx
You can check out partial/substring index options under Text for this. But it does add to the size of the index considerably.
Hi, great article but there are a few typ0s in the first box that uses json_textcontains with a few non matching quotes and a number hanging in the second query.
Thanks – my blog editor mangled some of the code. Fixing as I find them