XML, JSON and Oracle Text Search Index Enhancements in Oracle Database 23ai

In Oracle 23ai the CREATE SEARCH INDEX statement allows us to create search indexes on XML, JSON and text data, making the syntax consistent between them.

◉ XML Search Indexes

The index type of XDB.XMLIndex was introduced in a previous release to allow us to index XML data.

drop table if exists t1 purge;

create table t1 (

  id    number,

  data  xmltype

);

create index t1_xmlindex_idx on t1 (data) indextype is xdb.xmlindex;

In Oracle 23ai we can create search indexes for XML using the CREATE SEARCH INDEX ... FOR XML syntax. The full syntax is available here.

drop table if exists xml_tab purge;

create table xml_tab (

  id    number,

  data  xmltype

)

xmltype column data

store as transportable binary xml;

create search index xml_tab_search_idx on xml_tab (data) for XML

parameters (

  'search_on text'

);

The SEARCH_ON parameter must be set for XML search indexes.

• TEXT : Only text data is indexed for full-text search queries.
• VALUE(data_types) : Enables range-search for the specified data types. One or more of BINARY_DOUBLE, NUMBER, TIMESTAMP, VARCHAR2 as a comma-separated list.
• TEXT_VALUE(data_types) : A combination of support for full-text searches queries and range-search for the specified data types.

The XMLTYPE column must be stored as transportable binary XML to build a XML search index. For other storage types we can create an Oracle Text search index instead.

drop table if exists xml_tab purge;

create table xml_tab (

  id    number,

  data  xmltype

);

create search index xml_tab_search_idx on xml_tab (data);

◉ JSON Search Indexes

The original syntax for JSON search indexes in Oracle 12.1 was rather ugly.

drop table if exists t1 purge;

create table t1 (

  id    number,

  data  clob,

  constraint t1_json_chk check (data is json)

);

create index t1_search_idx on t1 (data)

  indextype is ctxsys.context

  parameters ('section group ctxsys.json_section_group sync (on commit)');

In Oracle 12.2 the JSON search index syntax was simplified using the CREATE SEARCH INDEX ... FOR JSON syntax. The full syntax is available here.

drop index if exists t1_search_idx;

create search index t1_search_idx on t1 (data) for json;

The SEARCH_ON parameter defaults to TEXT_VALUE for XML search indexes.

• NONE : JSON queries are not supported. Only the data guide index is maintained.
• TEXT : Only text data is indexed for full-text search queries.
• VALUE(data_types) : Enables range-search for the specified data types. One or more of NUMBER, TIMESTAMP, VARCHAR2 as a comma-separated list.
• TEXT_VALUE(data_types) : A combination of support for full-text searches queries and range-search for the specified data types.
• TEXT_VALUE_STRING : A combination of support for full-text searches queries and range-search for NUMBER, TIMESTAMP and VARCHAR2 data types.

◉ Oracle Text Search Indexes

Oracle has a long history of full-text indexing for text data. You can read about it here.

- Full Text Indexing using Oracle Text

In Oracle 23ai we can create full-text search indexes using the CREATE SEARCH INDEX syntax. The full syntax is available here.

drop table if exists text_tab purge;

create table text_tab (

  id    number,

  data  clob

);

create search index text_tab_search_idx on text_tab (data);

Source: oracle-base.com