8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23c » Here

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

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

Related articles.

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 23c 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.

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.

Oracle Text Search Indexes

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

In Oracle 23c 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);

For more information see:

Hope this helps. Regards Tim...

Back to the Top.