Notes

• When run, the DBMS_SEARCH.CREATE_INDEX procedure creates a JSON search index with predefined set of preferences and settings, enabled for performing full text search on multiple columns, tables, and views. An index table named INDEX_NAME is created with DATA and METADATA columns. This table is partitioned by OWNER and SOURCE, where OWNER specifies the table owner name and the SOURCE specifies the table or view name from which the data is indexed.

• You can define which tables or views should be indexed by adding them as data sources into your index. All the columns of those tables or views are indexed. Use the DBMS_SEARCH.ADD_SOURCE and DBMS_SEARCH.REMOVE_SOURCE procedures to manage data sources.

• The DBMS_SEARCH index is created with the following default indexing preferences:

  Preference	Description
  BASIC_WORDLIST	Enables wildcard indexing for a fast wildcard search.
  SEARCH_ON	Allows both the full-text and range-search queries for a specific data type. The supported data types are NUMBER (for indexing numeric values) and TIMESTAMP (for indexing date-time values).
  SYNC and OPTIMIZE	Automatically synchronizes and optimizes the DBMS_SEARCH index in the background at predefined intervals. You do not need to run the SYNC_INDEX and OPTIMIZE_INDEX operations on this index.

• You can query this index using the CONTAINS(), JSON_TEXTCONTAINS(), and JSON_EXISTS operators on the INDEX_NAME table.

Syntax

DBMS_SEARCH.CREATE_INDEX(
    index_name VARCHAR2,
    tablespace VARCHAR2 DEFAULT NULL
);

index_name

Specify name of the index that you want to create. You can specify schema.name.

tablespace

Specify name of the tablespace to contain the index or index partitions.

Example

CREATE TABLESPACE tbs_02 DATAFILE 'dt.dbf' size 100MB segment space management auto;

exec DBMS_SEARCH.CREATE_INDEX('MYINDEX','tbs_02');

Notes

• To add a data source, the index owner must have SELECT and DML access to the source.

• You can add multiple tables or views as data sources into the DBMS_SEARCH index (without the need to materialize the views). All data sources (table, view, or each table in the view definition) must include at least one Primary Key column.

  You can add only those views to this index that have a primary key and foreign key relationship with the component tables. All the component tables in the view source must also have primary key and foreign key relationships defined on them.

• The DBMS_SEARCH index stores all supported SQL data types (including Object Type columns) in JSON objects, except for the XMLTYPE and LONG data types. This means that you cannot add a table or view as a data source to the index if it has a column with the XMLTYPE or LONG data type. The maximum allowed length of a JSON data type is 32 megabytes.

• When run, the DBMS_SEARCH.ADD_SOURCE procedure creates background jobs at predefined intervals to synchronize and optimize the DBMS_SEARCH index with the DML changes on all data sources. You do not need to explicitly run the SYNC_INDEX and OPTIMIZE_INDEX operations on this index.

Syntax

DBMS_SEARCH.ADD_SOURCE(
  index_name IN VARCHAR2,
  source_name IN VARCHAR2);

index_name

Specify name of the index to which you want to add the table or view.

source_name

Specify name of the table or view that you want to add to the index.

Examples

exec DBMS_SEARCH.ADD_SOURCE('MYINDEX','MYTABLE');

exec DBMS_SEARCH.ADD_SOURCE('MYINDEX','MYVIEW');

When run, this procedure deletes all indexed data and stops further indexing or maintenance operations on the associated data sources (tables or views).

Syntax

DBMS_SEARCH.REMOVE_SOURCE(
  index_name VARCHAR2, 
  source_name VARCHAR2);

index_name

Specify name of the index from which you want to remove the table or view.

source_name

Specify name of the table or view that you want to remove.

Example

exec DBMS_SEARCH.REMOVE_SOURCE('MYINDEX','MYTABLE');

Syntax

DBMS_SEARCH.DROP_INDEX(
  INDEX_NAME VARCHAR2);

index_name

Specify name of the index that you want to drop.

Example

exec DBMS_SEARCH.DROP_INDEX('MYINDEX');

Syntax

DBMS_SEARCH.GET_DOCUMENT(
  index_name VARCHAR2,
  metadata JSON
);

index_name

Specify name of the index for which you want to retrieve the data.

metadata

Specify the JSON metadata values, such as OWNER, SOURCE, or KEY. You must specify the metadata format based on the METADATA column of the INDEX_NAME table.

Example

SELECT DBMS_SEARCH.GET_DOCUMENT('MYINDEX',METADATA) from MYINDEX;

You can compute aggregations on different fields of the JSON data. The query lists search results in the JSON Results Set Interface, which supports faceted navigation and aggregations.

Syntax

DBMS_SEARCH.FIND(
  index_name VARCHAR2, 
  search_QBE JSON);

index_name

Specify name of the index on which you want to perform the query.

search_QBE

Specify the result_set_descriptor parameter value in JSON. It describes what the result set should contain.

The JSON format input result set descriptor consists of the $query, $search, and $facet parts:

{
  "$query":text query and filter conditions,
  "$search":search result specifications,
  "$facet":faceted result specifications	
}

For details on each of these JSON objects, see The JSON Format Input Result Set Descriptor.

The JSON format output result set descriptor consists of the following parts:

"$count":number
"$hit":[ hit_object_1, ..., hit_object_i , ... ]
"$facet":[ facet_object_1, ..., facet_object_i, ...]

For details on each of these JSON objects, see The JSON Format Result Set Output.

Example

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
connect sys/knl_example as sysdba;
Connected.

grant connect,resource, unlimited tablespace, ctxapp
  to u1 identified by u1;
Grant succeeded.

connect u1/u1;
Connected.

create table tbl(id number primary key, jsn_col clob check(jsn_col is json));
Table created.

INSERT INTO tbl
  VALUES (1,'{ "zebra" : { "price" : [2000,1000],
                         "name" : "Marty",
                         "stripes" : ["Dark","Light"],
                         "handler" : "Bob", "sold" : true }}');
1 row created.

INSERT INTO tbl
  VALUES (2,'{ "zebra" : { "rating": 5, "price" : 1000,
                         "name" : "Zigby",
                         "stripes" : ["Light","Grey"],
                         "handler" : "Handy Marty", "sold" : "true" }}');
1 row created.
 
 INSERT INTO tbl
   VALUES (3,'{ "zebra" : { "rating": 4.5, "price" : 3000,
                          "name" : "Zigs",
                          "stripes" : ["Grey","Dark"],
                          "handler" : "Handy Marty", "sold" : false }}');
1 row created.
 
 INSERT INTO tbl
   VALUES (4,'{ "zebra" : { "rating": "4.5", "price" : "3000",
                          "name" : "Zigs",
                          "stripes" : ["Grey","Dark"],
                          "handler" : "Handy Marty", "sold" : null }}');
1 row created.
 
 commit;
Commit complete.

SQL> exec DBMS_SEARCH.CREATE_INDEX('JIDX');
PL/SQL procedure successfully completed.
SQL> exec DBMS_SEARCH.ADD_SOURCE('JIDX','TBL');
PL/SQL procedure successfully completed.
SQL> 

Query: All zebras having name starting with Zig or having name Marty and
having a price greater than equal to 2000
Facets: For all zebras that satisfy the query, do the following
--   1. Get the count of zebras per zebra handler
--   2. Get the minimum zebra rating
--   3. Get the count of zebras for each unique stripe color
select DBMS_SEARCH.FIND('JIDX',JSON('
  {
    "$query": { "$and" : [
                    { "U1.TBL.JSN_COL.zebra.name" : { "$contains" : "Zig% or Marty" } },
                    { "U1.TBL.JSN_COL.zebra.price" : { "$gte" : 2000 } }
                  ]
                },
      "$facet" : [
        { "$uniqueCount" : "U1.TBL.JSN_COL.zebra.handler" },
        { "$min" : "U1.TBL.JSN_COL.zebra.rating" },
        { "$uniqueCount" : "U1.TBL.JSN_COL.zebra.stripes" }
      ]
    }'));

FIND_RESULT
--------------------------------------------------------------------------------
{
  "$count" : 3,
  "$facet" :
  [
    {
      "U1.TBL.JSN_COL.zebra.handler" :
      [
        {
          "value" : "Handy Marty",
          "$uniqueCount" : 2
        },
        {
          "value" : "Bob",
          "$uniqueCount" : 1
        }
      ]
    },
    {
      "U1.TBL.JSN_COL.zebra.rating" :
      {
        "$min" : 4.5
      }
    },
    {
      "U1.TBL.JSN_COL.zebra.stripes" :
      [
        {
          "value" : "Dark",
          "$uniqueCount" : 3
        },
        {
          "value" : "Grey",
          "$uniqueCount" : 2
        },
        {
          "value" : "Light",
          "$uniqueCount" : 1
        }
      ]
    }
  ]
}
1 row selected.
connect sys/knl_example as sysdba;
Connected.
drop user u1 cascade;
User dropped.